我有一个存储过程，它返回80列和300行。我要写一个select函数，它能得到2个这样的列。类似的
SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'
当我使用上面的语法时，我得到了错误:
“无效的列名”。
我知道最简单的解决方案是更改存储过程，但我没有编写它，也不能更改它。
有什么办法能让我如愿以偿吗?
I could make a temp table to put the results in, but because there are 80 columns so I would need to make an 80 column temp table just to get 2 columns. I wanted to avoid tracking down all the columns that are returned.
I tried using WITH SprocResults AS .... as suggested by Mark, but I got 2 errors
Incorrect syntax near the keyword 'EXEC'.Incorrect syntax near ')'.
I tried declaring a table variable and I got the following error
Insert Error: Column name or number of supplied values does not match table definition
If I try
SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
I get the error :
Incorrect syntax near the keyword 'exec'.
对于任何使用SQL 2012或更高版本的用户，我都能够使用非动态且每次都有相同列输出的存储过程来实现这一点。
总体思想是构建动态查询来创建、插入、选择和删除临时表，并在生成所有临时表后执行。首先从存储过程中检索列名和类型，动态生成临时表。
注意:如果您愿意/能够更新SP或更改配置并使用OPENROWSET，那么有更好、更通用的解决方案，它们只需要更少的代码行。如果没有其他方法，请使用下面的方法。
DECLARE @spName VARCHAR(MAX) = 'MyStoredProc'
DECLARE @tempTableName VARCHAR(MAX) = '#tempTable'
-- might need to update this if your param value is a string and you need to escape quotes
DECLARE @insertCommand VARCHAR(MAX) = 'INSERT INTO ' + @tempTableName + ' EXEC MyStoredProc @param=value'
DECLARE @createTableCommand VARCHAR(MAX)
-- update this to select the columns you want
DECLARE @selectCommand VARCHAR(MAX) = 'SELECT col1, col2 FROM ' + @tempTableName
DECLARE @dropCommand VARCHAR(MAX) = 'DROP TABLE ' + @tempTableName
-- Generate command to create temp table
SELECT @createTableCommand = 'CREATE TABLE ' + @tempTableName + ' (' +
STUFF
(
(
SELECT ', ' + CONCAT('[', name, ']', ' ', system_type_name)
FROM sys.dm_exec_describe_first_result_set_for_object
(
OBJECT_ID(@spName),
NULL
)
FOR XML PATH('')
)
,1
,1
,''
) + ')'
EXEC( @createTableCommand + ' '+ @insertCommand + ' ' + @selectCommand + ' ' + @dropCommand)
如果您这样做是为了手动验证数据，您可以使用LINQPad。
在LinqPad中创建一个到数据库的连接，然后创建类似于下面的c#语句:
DataTable table = MyStoredProc (param1, param2).Tables[0];
(from row in table.AsEnumerable()
select new
{
Col1 = row.Field<string>("col1"),
Col2 = row.Field<string>("col2"),
}).Dump();
参考http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet.aspx
正如问题中提到的，在执行存储过程之前很难定义80列的临时表。
另一种方法是根据存储过程结果集填充表。
SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;'
,'EXEC MyStoredProc')
如果您得到任何错误，您需要通过执行以下查询来启用特定的分布式查询。
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
要使用两个参数执行sp_configure以更改配置选项或运行RECONFIGURE语句，必须授予ALTER SETTINGS服务器级权限
现在可以从生成的表中选择特定的列
SELECT col1, col2
FROM #temp
对于任何使用SQL 2012或更高版本的用户，我都能够使用非动态且每次都有相同列输出的存储过程来实现这一点。
总体思想是构建动态查询来创建、插入、选择和删除临时表，并在生成所有临时表后执行。首先从存储过程中检索列名和类型，动态生成临时表。
注意:如果您愿意/能够更新SP或更改配置并使用OPENROWSET，那么有更好、更通用的解决方案，它们只需要更少的代码行。如果没有其他方法，请使用下面的方法。
DECLARE @spName VARCHAR(MAX) = 'MyStoredProc'
DECLARE @tempTableName VARCHAR(MAX) = '#tempTable'
-- might need to update this if your param value is a string and you need to escape quotes
DECLARE @insertCommand VARCHAR(MAX) = 'INSERT INTO ' + @tempTableName + ' EXEC MyStoredProc @param=value'
DECLARE @createTableCommand VARCHAR(MAX)
-- update this to select the columns you want
DECLARE @selectCommand VARCHAR(MAX) = 'SELECT col1, col2 FROM ' + @tempTableName
DECLARE @dropCommand VARCHAR(MAX) = 'DROP TABLE ' + @tempTableName
-- Generate command to create temp table
SELECT @createTableCommand = 'CREATE TABLE ' + @tempTableName + ' (' +
STUFF
(
(
SELECT ', ' + CONCAT('[', name, ']', ' ', system_type_name)
FROM sys.dm_exec_describe_first_result_set_for_object
(
OBJECT_ID(@spName),
NULL
)
FOR XML PATH('')
)
,1
,1
,''
) + ')'
EXEC( @createTableCommand + ' '+ @insertCommand + ' ' + @selectCommand + ' ' + @dropCommand)
