SQLTeam.com | Weblogs | Forums

Export Stored Procedure to Excel


#1

Sir,

I am new to SQL. I had created a Stored Procedure. In the end of stored procedure, how to write the code to export the results to EXCEL?

Thanks Sir.


#2

hi guna

:slight_smile:
:slight_smile:

as discussed

`

use SP like this ..example

exec [dbo].[Guna] 2019 , 1 ,'E:\exported.csv'

`

SP with BCP added
USE [TBOMS]
go 
/****** Object:  StoredProcedure [dbo].[Guna]    Script Date: 01/24/2019 15:35:14 ******/

ALTER PROCEDURE [dbo].[Guna] 
  @year  INT , 
  @month INT , 
  @exportFile nvarchar(50)
AS 
  BEGIN 
    
    IF Object_id('dbo.temp', 'U') IS NOT NULL 
    BEGIN 
      DROP TABLE dbo.temp 
    END 
    
    SELECT DISTINCT head_name , 
                    b.head_id 
    INTO            temp 
    FROM            tbl_employee a 
    JOIN            tbl_fse b 
    ON              a.emp_code = b.emp_code 
    JOIN            tbl_head c 
    ON              b.head_id = c.head_id 
    
    DECLARE @colsUnpivot AS NVARCHAR(max) 
    DECLARE @query AS       NVARCHAR(max) 
    
    SELECT @colsUnpivot = Stuff( 
           ( 
                    SELECT   ',' + Quotename(head_name) 
                    FROM     temp 
                    ORDER BY head_id FOR xml path('')), 1, 1, '') 
    PRINT @colsUnpivot 
    
    IF Object_id('dbo.abc', 'U') IS NOT NULL 
    BEGIN 
      DROP TABLE dbo.abc 
    END 
    
    SET @query = ' SELECT * into abc  FROM (    select a.emp_code,a.emp_name,b.sal_month,Head_Name,FSE_Amount from tbl_employee  a join tbl_fse b  on a.Emp_Code = b.Emp_Code join tbl_head c  on b.Head_ID = c.Head_ID ) as s PIVOT (     SUM(FSE_Amount)     FOR [Head_Name] IN (' + @colsUnpivot + ') ) AS pvt'
    
    EXEC sp_executesql 
      @query; 
    
    IF Object_id('dbo.temp1', 'U') IS NOT NULL 
    BEGIN 
      DROP TABLE dbo.temp1        
    END 
    
    SELECT DISTINCT head_name , 
                    b.head_id 
    INTO            temp1 
    FROM            tbl_employee a 
    JOIN            tbl_fsd b 
    ON              a.emp_code = b.emp_code 
    JOIN            tbl_head c 
    ON              b.head_id = c.head_id 
    
    DECLARE @colsUnpivot1 AS NVARCHAR(max) 
   
    DECLARE @query1 AS       NVARCHAR(max) 
   
    SELECT @colsUnpivot1 = Stuff( 
           ( 
                    SELECT   ',' + Quotename(head_name) 
                    FROM     temp1 
                    ORDER BY head_id FOR xml path('')), 1, 1, '') 
    
    PRINT @colsUnpivot1 
    
    IF Object_id('dbo.def', 'U') IS NOT NULL 
    BEGIN 
      DROP TABLE dbo.def 
    END 
    
    SET @query1 = ' SELECT * into def FROM (    select a.emp_code,a.emp_name,b.sal_month,Head_Name,FSD_Amount from tbl_employee  a join tbl_fsd b  on a.Emp_Code = b.Emp_Code join tbl_head c  on b.Head_ID = c.Head_ID ) as s PIVOT (     SUM(FSD_Amount)     FOR [Head_Name] IN (' + @colsUnpivot1 + ') ) AS pvt'
    
    EXEC sp_executesql 
      @query1; 
    
    IF Object_id('dbo.xyz', 'U') IS NOT NULL 
    BEGIN 
      DROP TABLE dbo.xyz 
    END ;
     
    ;WITH fse_cte AS 
    ( 
             SELECT   a.emp_code, 
                      Sum(fse_amount) AS sumfse 
             FROM     tbl_employee a 
             JOIN     tbl_fse b 
             ON       a.emp_code = b.emp_code 
             JOIN     tbl_head c 
             ON       b.head_id = c.head_id 
             WHERE  Month(b.sal_month) = @month 
    AND    Year(b.sal_month) = @year 
             GROUP BY a.emp_code ) 
          , fsd_cte AS 
    ( 
             SELECT   a.emp_code, 
                      Sum(fsd_amount) AS sumfsd 
             FROM     tbl_employee a 
             JOIN     tbl_fsd b 
             ON       a.emp_code = b.emp_code 
             JOIN     tbl_head c 
             ON       b.head_id = c.head_id 
              WHERE  Month(b.sal_month) = @month 
    AND    Year(b.sal_month) = @year 
             GROUP BY a.emp_code ) 
             
    SELECT a.emp_code , sumfse,sumfsd,
           (sumfse - sumfsd) AS sumdiff 
    INTO   xyz 
    FROM   fse_cte a 
    JOIN   fsd_cte b 
    ON     a.emp_code = b.emp_code 
    
    
    IF Object_id('dbo.EXPORTEXCEL', 'U') IS NOT NULL 
    BEGIN 
      DROP TABLE dbo.EXPORTEXCEL        
    END 
    
    
    SELECT * 
             FROM   abc a 
    JOIN   def b 
    ON     a.emp_code = b.emp_code 
    JOIN   xyz c 
    ON     a.emp_code = c.emp_code 
   WHERE  Month(b.sal_month) = @month 
    AND    Year(b.sal_month) = @year 
    
BEGIN
SET NOCOUNT ON;
EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE; 
EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE; 
Declare @SQL nvarchar(4000)
Set @SQL = 'SELECT * FROM   TBOMS..abc a  JOIN   TBOMS..def b ON     a.emp_code = b.emp_code  JOIN   TBOMS..xyz c ON     a.emp_code = c.emp_code  WHERE  Month(b.sal_month) = '+CAST( @month AS VARCHAR) +' AND    Year(b.sal_month) = '+ CAST( @year AS VARCHAR) + ''
    
--select @SQL
Declare @cmd nvarchar(4000)
SET @cmd = 'bcp '+CHAR(34)+@SQL+CHAR(34)+' queryout '+CHAR(34)+@exportFile+CHAR(34)+' -S '+@@servername+' -c -t'+CHAR(34)+','+CHAR(34)+' -r'+CHAR(34)+'\n'+CHAR(34)+' -T'
PRINT @CMD 
exec master..xp_cmdshell @cmd
EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE; 
EXEC sp_configure 'show advanced options', 0;  
RECONFIGURE;

END  
  END

#3

would you like this to be automated or be run manually?


#4

Yosiasz
:grin::grin:

Talking about SSIS


#5

:slightly_smiling_face: or SQL Job


#6

Do you need the results in Excel format - or just able to open in Excel? Excel will open CSV files in Excel and they are much easier to create than actual Excel files (and the users can save as Excel format if that is what they truly need).


#7

Coming from an MS Excel standpoint, If you would like the result to be refreshed from MS Excel, there are two options.

Option 1: VBA with ADO to call the SP and dump the data. I can write the VBA code for this but recommend option 2 first.

Option 2 (which I recommend for you):
Go to the tab "Data"
Click on "From Other Sources"
Select "From Microsoft Query"
Select "New Data Sources"
Build your connection if you are using Window Authentication on AD.
Skip the optional fields
Cancel all wizards until you get to the window "Microsoft Query"
Click on the button "SQL".
Enter your SP like: EXEC YourStoredProc 'First Parameter'
Once you get the results, click on the menu "File" and then "Return Data to Microsoft Excel"

You can then refresh the result set with the button "Refresh All"


#8

Also the pro of doing the above is that you can keep all of your ms excel formatting and functionalities. The con is that if you share the file, the other person may need to setup the connection on their side, unless you copy the result to a new sheet.

The other approach is using vba which will resolve the connection setting as long as those who needs to refresh data has access to the stored proc. However, noted that if you are on office 365 online, I believe vba is not supported.


#9

or move away from excel and towards SSRS


#10

Agree if he has an SSRS environment setup.


#11

The problem with this approach is that every user now needs direct access to SQL Server with permissions to execute that stored procedure. And if that procedure accesses data in a separate database the user will need access to the other database(s).

If you don't have SSRS - or you don't need the overhead of creating a report that is scheduled to run and send an email - you can easily use sp_send_dbmail to deliver a CSV file that can be opened directly in Excel.

There is one trick that needs to be utillized - which is the first column header in the results needs to tell Excel that this is a CSV file. This is done using some like:

 Select x.ColumnValue As [Sep=,' + char(13) + char(10) + 'ColumnName]

This uses dynamic SQL that is passed to sp_send_dbmail to be executed...here is a template:

Declare @query nvarchar(max) = ''
      , @recipients varchar(max) = 'user1@domain.com;user2@domain.com'
      , @cc_recipients varchar(max) = 'user3@domain.com;user4@domain.com';   

    Set @query = '
    Set Nocount On;

 Select x.ColumnValue As [Sep=,' + char(13) + char(10) + 'ColumnName]
      , x.Column2
      , x.Column3
   From dbo.MyTable                     x
  Where x.SomeColum = ''somevalue'';'
  
Execute msdb.dbo.sp_send_dbmail
        @profile_name = '{db mail profile}'
      , @query = @query
      , @subject = '{email subject}' 
      , @body = '{body text - can be in HTML or plain text}'
      , @recipients = @recipients
      , @copy_recipients = @cc_recipients
      , @execute_query_database = '{database}'
      , @attach_query_result_as_file = 1
      , @query_result_width = 8000
      , @query_attachment_filename = '{file name}.csv'
      , @query_result_header = 1
      , @query_result_separator = ','
      , @query_result_no_padding = 1;

#12

[quote="jeffw8713, post:11, topic:14898"]
The problem with this approach is that every user now needs direct access to SQL Server with permissions to execute that stored procedure. And if that procedure accesses data in a separate database the user will need access to the other database(s).
[/quote]Hi Jeff, this is a question for my own learning curve since I just posted a question about schema. I thought that when a SP is granted permission, then all tables within that SP will have granted permission? Can't SELECT the table directly but the SP will SELECT those tables. At least this is my understanding with Schema and Permission. So when they are in other databases, it will not work?

OP, sorry to off track your original question a bit.


#13

If a stored procedure accesses data in multiple databases - the user executing that procedure will require access to those other databases. To get around that you can create your procedure with a credential that has access and setup the procedure to execute as that credential.

If the procedure is self-contained and only accesses data within that single database - then a user only needs access to the database and execute permissions on that procedure.

You could also setup cross-database ownership chaining - but that is not recommended as it opens a whole set of potential security issues.