将存储过程结果集导出到SSMS中的Excel [英] Export stored procedure result set to Excel in SSMS

问题描述

我正在使用SSMS，并尝试将存储过程的结果导出到新的Excel文件中. SP接受一个int参数，但是我找不到在查询中调用它的方法.

i'm using SSMS and attempting to export the results of a stored procedure to a new excel file. The SP accepts an int parameter but I cannot find a way to call it in the query.

最新的努力-

EXEC sp_makewebtask 
    @outputfile = 'C:\Users\me\Documents\testing.xls', 
    @query = **ExportAsExcel** N'@id' = 123
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

运行存储过程将产生两个数据表，这些数据需要在单独的工作表上使用.你们中的任何人都可以建议一种更好的方法吗?它甚至不需要自动化，我只需要获取正确的数据即可. sp名称在上面加粗了.

Running the stored procedure results in two tables of data, which I need on separate sheets. Can any of you advise a better way to go about this? It doesn't even need to be automated, I just need to get the correct data. The sp name is bolded above.

感谢您的时间，

H

推荐答案

我建议您将存储过程分为两个过程，每个过程分别返回一个单独的表，并分别调用不同的工作表.

I suggest you split your stored procedure into two procedures that each respectively return a separate table and have those called to different worksheets.

有多种方法可以使用SQL将数据返回到Excel

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):

1. 选择Excel功能区上的数据"选项卡，然后在获取外部数据"组中，选择来自其他来源"下拉列表.然后选择来自Microsoft查询"

1. 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"

在选择数据源"弹出框中，选择您的SQL Server，然后单击确定".

Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.

如有必要，关闭添加表"弹出窗口.

Close the "Add Tables" popup if necessary.

单击"SQL"按钮，或选择视图">"SQL"以打开SQL弹出编辑器.

Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.

输入以下语法:{CALL myDatabaseName.dbo.myStoredProc(?，?，?)}

Enter the following syntax: {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}

例如:{CALL northwind.dbo.spGetMaxCost(?，?，?)}

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.

单击确定"按钮.弹出一个问题框，提示"SQL Query无法以图形方式表示，是否继续?"，只需单击确定"按钮即可.

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.

一旦输入了最后一个参数，您应该在Microsoft Query中获得一些结果.如果看起来不错，请关闭Microsoft Query.

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.

选择从以下单元格中获取值"，然后通过单击带箭头的小框，将其连接到将保存参数的Excel中适当的单元格.

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:

连接信息

而且，如果您使用工作表中的参数，例如，

And, if you use parameters from sheet then, for my example,

这篇关于将存储过程结果集导出到SSMS中的Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！