从存储过程的结果集中选择列

您可以拆分查询吗?将存储的过程结果插入到表变量或临时表中。然后，从表变量中选择2列。

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'


SELECT col1, col2 FROM @tablevar

这里有一个链接，指向一个非常好的文档，它解释了解决您的问题的所有不同方法(尽管其中许多方法不能使用，因为您不能修改现有的存储过程)。

如何在存储过程之间共享数据

Gulzar的答案是可行的(在上面的链接中有文档)，但是编写起来会很麻烦(您需要在@tablevar(col1，…)语句中指定所有80个列名。在将来，如果一个列被添加到模式中，或者输出被改变，它将需要在您的代码中更新，否则它将出错。

(假设SQL Server)

在T-SQL中处理存储过程结果的唯一方法是使用INSERT INTO ... EXEC语法。这样就可以向临时表或表变量中插入数据，并从中选择所需的数据。

试试这个

use mydatabase
create procedure sp_onetwothree as
select 1 as '1', 2 as '2', 3 as '3'
go
SELECT a.[1], a.[2]
FROM OPENROWSET('SQLOLEDB','myserver';'sa';'mysapass',
'exec mydatabase.dbo.sp_onetwothree') AS a
GO

所以像SELECT * FROM (exec sp_tables) Table1这样的东西将不起作用

CREATE TABLE #Result
(
ID int,  Name varchar(500), Revenue money
)
INSERT #Result EXEC RevenueByAdvertiser '1/1/10', '2/1/10'
SELECT * FROM #Result ORDER BY Name
DROP TABLE #Result

这适用于我:(即我只需要sp_help_job返回的30+的2列)

SELECT name, current_execution_status
FROM OPENQUERY (MYSERVER,
'EXEC msdb.dbo.sp_help_job @job_name = ''My Job'', @job_aspect = ''JOB''');

在这之前，我需要运行这个:

sp_serveroption 'MYSERVER', 'DATA ACCESS', TRUE;

．...来更新sys.servers表。(例如，在OPENQUERY中使用自引用默认情况下是禁用的。)

对于我的简单要求，我没有遇到Lance的优秀链接的OPENQUERY节中描述的问题。

Rossini，如果你需要动态地设置这些输入参数，那么OPENQUERY的使用就变得更加精细了:

DECLARE @innerSql varchar(1000);
DECLARE @outerSql varchar(1000);


-- Set up the original stored proc definition.
SET @innerSql =
'EXEC msdb.dbo.sp_help_job @job_name = '''+@param1+''', @job_aspect = N'''+@param2+'''' ;


-- Handle quotes.
SET @innerSql = REPLACE(@innerSql, '''', '''''');


-- Set up the OPENQUERY definition.
SET @outerSql =
'SELECT name, current_execution_status
FROM OPENQUERY (MYSERVER, ''' + @innerSql + ''');';


-- Execute.
EXEC (@outerSql);

我不确定使用sp_serveroption直接更新现有的sys.servers自引用与使用sp_addlinkedserver(如Lance的链接中所述)创建副本/别名之间的区别(如果有的话)。

但是在这个例子中，我不能使用INSERT…EXEC构造，因为sp_help_job已经使用了一个。(“INSERT EXEC语句不能嵌套”)

一个快速的破解方法是添加一个新的参数'@Column_Name'，并让调用函数定义要检索的列名。在你的sproc的返回部分，你会有if/else语句并只返回指定的列，或者如果为空-返回所有。

CREATE PROCEDURE [dbo].[MySproc]
@Column_Name AS VARCHAR(50)
AS
BEGIN
IF (@Column_Name = 'ColumnName1')
BEGIN
SELECT @ColumnItem1 as 'ColumnName1'
END
ELSE
BEGIN
SELECT @ColumnItem1 as 'ColumnName1', @ColumnItem2 as 'ColumnName2', @ColumnItem3 as 'ColumnName3'
END
END

如果您这样做是为了手动验证数据，您可以使用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

如果你只需要这样做一次，最简单的方法是:

在导入和导出向导中导出到excel，然后将此excel导入到表中。

我会剪切粘贴原始SP并删除所有列，除了你想要的两列。或。我将返回结果集，将其映射到适当的业务对象，然后LINQ出这两列。

要实现这一点，首先你创建一个#test_table，如下所示:

create table #test_table(
col1 int,
col2 int,
.
.
.
col80 int
)

现在执行过程并将值放入#test_table:

insert into #test_table
EXEC MyStoredProc 'param1', 'param2'

现在你从#test_table中获取值:

select col1,col2....,col80 from #test_table

如果你能够修改你的存储过程，你可以很容易地把所需的列定义作为一个参数，并使用一个自动创建的临时表:

CREATE PROCEDURE sp_GetDiffDataExample
@columnsStatement NVARCHAR(MAX) -- required columns statement (e.g. "field1, field2")
AS
BEGIN
DECLARE @query NVARCHAR(MAX)
SET @query = N'SELECT ' + @columnsStatement + N' INTO ##TempTable FROM dbo.TestTable'
EXEC sp_executeSql @query
SELECT * FROM ##TempTable
DROP TABLE ##TempTable
END

在这种情况下，您不需要手动创建临时表—它是自动创建的。希望这能有所帮助。

对于SQL Server，我发现这工作得很好:

创建一个临时表(或永久表，并不重要)，并对存储过程执行insert into语句。SP的结果集应该与表中的列匹配，否则将得到一个错误。

这里有一个例子:

DECLARE @temp TABLE (firstname NVARCHAR(30), lastname nvarchar(50));


INSERT INTO @temp EXEC dbo.GetPersonName @param1,@param2;
-- assumption is that dbo.GetPersonName returns a table with firstname / lastname columns


SELECT * FROM @temp;

就是这样!

正如问题中提到的，在执行存储过程之前很难定义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)

我知道从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

这里有一个简单的答案:

SELECT ColA, ColB
FROM OPENROWSET('SQLNCLI','server=localhost;trusted_connection=yes;','exec schema.procedurename')

SQLNCLI是本地SQL客户端，而“;localhost"将使它利用您正在其上执行过程的服务器。

不需要构建临时表或任何其他jazz。