Export stored procedure result set to Excel in SSMS
I suggest you split your stored procedure into two procedures that each respectively return a separate table and have those called to different worksheets.
There are a variety of ways to return data to Excel using SQL
Here is a favourite of mine from code by Joshua (you don't have to use the parameters):
Select the Data tab on Excel's Ribbon, then within the Get Exernal Data group choose the "From other Sources" drop-down. Then Choose "From Microsoft Query"
Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.
Close the "Add Tables" popup if necessary.
Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.
Enter the following syntax: {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}
For example: {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}
Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.
Hit the OK button. A question box should pop-up saying "SQL Query can't be represented graphically, continue anyway?", just hit the OK button.
You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.
Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.
You should now be looking at an "Import Data" pop-up. Click the Properties button, which will bring up the "Connection Properties" pop-up.
Select the Definition tab, then select the Parameters button. You should now see a "Parameters" pop-up, where you can connect the parameter to a specific cell.
Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.
If you want the data to refresh every time you change the cell containing the parameter, check the box stating "Refresh automatically when cell value changes"
Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.
You should now have some data straight from your stored procedure.
You will end up with connection information similar to:
Connection info
And, if you use parameters from sheet then, for my example,
Exporting the output of a stored proc. to excel
You can use INSERT INTO OPENROWSET, for example:
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Output.xls;', 'EXEC [dbo].[spName] ''param''') EXEC [dbo].[spName] 'param'
You should enable Ad Hoc Distributed Queries before:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
SQL Server stored procedure to export Select Result to CSV
The task was I had to export from database some data to .CSV at specified time. In the begining we wanted to use windows scheduler for running stp. The STP had to be able to export data. But I couldn't find a way. Instead the thing what we did was creating simple STP which brings only data . And we created batch file which calls STP and export result to .CSV file. The batch file is simple
sqlcmd -S Etibar-PC\SQLEXPRESS -d MEV_WORK -E -Q "dbo.SelectPeople" -o "MyData1.csv" -h-1 -s"," -w 700
dbo.SelectPeople is STP
Etibar-PC\SQLEXPRESS is Schema
MEV_WORK is Database name.
Related Topics
Webgl - Wait for Texture to Load
Bootstrap 3 Flush Footer to Bottom. Not Fixed
Subpixel Anti-Aliased Text on HTML5's Canvas Element
Send Form by Email and Track Responses in Spreadsheet
Make a Parent Div Webkit-Filter Not Affect Children
Websocket Server Implementations for Delphi
Texture Splatting with Three.Js
Is It Possible Put Image in Input Type="Check Box"
CSS 100% Height, and Then Scroll Div Not Page
Do Custom CSS Properties Use One Leading Dash or Two
Achieving Min-Width with Viewport Meta Tag
How Might I Force a Floating Div to Match The Height of Another Floating Div
Django Doesn't Display Newline Character When Rendering Text from Database