我有一个存储过程，它返回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'.
如果您这样做是为了手动验证数据，您可以使用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
我知道从sp执行和插入到临时表或表变量将是一个选项，但我不认为这是你的要求。根据您的要求，以下查询语句应该工作:
Declare @sql nvarchar(max)
Set @sql='SELECT col1, col2 FROM OPENROWSET(''SQLNCLI'', ''Server=(local);uid=test;pwd=test'',
''EXEC MyStoredProc ''''param1'''', ''''param2'''''')'
Exec(@sql)
如果你有可信连接，那么使用下面的查询语句:
Declare @sql nvarchar(max)
Set @sql='SELECT col1, col2 FROM OPENROWSET(''SQLNCLI'', ''Server=(local);Trusted_Connection=yes;'',
''EXEC MyStoredProc ''''param1'''', ''''param2'''''')'
Exec(@sql)
如果运行上面的语句出错，那么只需运行下面的语句:
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
我希望这将帮助那些将面临类似问题的人。如果有人想尝试temp表或表变量，应该如下所示，但在这种情况下，你应该知道sp返回多少列，然后你应该在temp表或表变量中创建这么多列:
--for table variable
Declare @t table(col1 col1Type, col2 col2Type)
insert into @t exec MyStoredProc 'param1', 'param2'
SELECT col1, col2 FROM @t
--for temp table
create table #t(col1 col1Type, col2 col2Type)
insert into #t exec MyStoredProc 'param1', 'param2'
SELECT col1, col2 FROM #t
创建一个动态视图，并从中获得结果.......
CREATE PROCEDURE dbo.usp_userwise_columns_value
(
@userid BIGINT
)
AS
BEGIN
DECLARE @maincmd NVARCHAR(max);
DECLARE @columnlist NVARCHAR(max);
DECLARE @columnname VARCHAR(150);
DECLARE @nickname VARCHAR(50);
SET @maincmd = '';
SET @columnname = '';
SET @columnlist = '';
SET @nickname = '';
DECLARE CUR_COLUMNLIST CURSOR FAST_FORWARD
FOR
SELECT columnname , nickname
FROM dbo.v_userwise_columns
WHERE userid = @userid
OPEN CUR_COLUMNLIST
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
FETCH NEXT FROM CUR_COLUMNLIST
INTO @columnname, @nickname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @columnlist = @columnlist + @columnname + ','
FETCH NEXT FROM CUR_COLUMNLIST
INTO @columnname, @nickname
END
CLOSE CUR_COLUMNLIST
DEALLOCATE CUR_COLUMNLIST
IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'v_userwise_columns_value')
BEGIN
SET @maincmd = 'CREATE VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , '
+ CHAR(39) + @nickname + CHAR(39) + ' as nickname, '
+ @columnlist + ' compcode FROM dbo.SJOTran '
END
ELSE
BEGIN
SET @maincmd = 'ALTER VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , '
+ CHAR(39) + @nickname + CHAR(39) + ' as nickname, '
+ @columnlist + ' compcode FROM dbo.SJOTran '
END
--PRINT @maincmd
EXECUTE sp_executesql @maincmd
END
-----------------------------------------------
SELECT * FROM dbo.v_userwise_columns_value
