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.
hi guna
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
would you like this to be automated or be run manually?
Yosiasz
Talking about SSIS
or SQL Job
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).
1 Like
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"
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.
or move away from excel and towards SSRS
Agree if he has an SSRS environment setup.
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;
1 Like
[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.
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.