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

I have a stored procedure that returns 80 columns, and 300 rows. I want to write a select that gets 2 of those columns. Something like

我有一个返回80列和300行的存储过程。我想写一个select来获取其中的两列。类似的

SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'

When I used the above syntax I get the error:

当我使用上述语法时，我得到了错误:

"Invalid Column Name".

“无效列名”。

I know the easiest solution would be to change the stored procedure, but I didn't write it, and I can't change it.

我知道最简单的解决方案是更改存储过程，但我没有编写它，也无法更改它。

Is there any way to do what I want?

有办法做我想做的事吗?

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.

我可以做一个临时表来输入结果，但是因为有80列所以我需要做一个80列临时表来得到2列。我想避免跟踪返回的所有列。
I tried using WITH SprocResults AS .... as suggested by Mark, but I got 2 errors

我试着使用与SprocResults ....如Mark所说，我有两个错误

Incorrect syntax near the keyword 'EXEC'.
Incorrect syntax near ')'.

在关键字“EXEC”附近的语法错误。不正确的语法)的附近。
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 :

如果我尝试从exmystoredproc 'param1'， 'param2'中选择*，我会得到错误:

Incorrect syntax near the keyword 'exec'.

在关键字“exec”附近的语法错误。

16 个解决方案

#1

151

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

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

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

SELECT col1, col2 FROM @tablevar

#2

Here's a link to a pretty good document explaining all the different ways to solve your problem (although a lot of them can't be used since you can't modify the existing stored procedure.)

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

How to Share Data Between Stored Procedures

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

Gulzar's answer will work (it is documented in the link above) but it's going to be a hassle to write (you'll need to specify all 80 column names in your @tablevar(col1,...) statement. And in the future if a column is added to the schema or the output is changed it will need to be updated in your code or it will error out.

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

#3

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

Source:
http://stevesmithblog.com/blog/select-from-a-stored-procedure/

来源:http://stevesmithblog.com/blog/select-from-a-stored-procedure/

#4

This works for me: (i.e. I only need 2 columns of the 30+ returned by sp_help_job)

这对我来说是可行的(例如，我只需要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''');

Before this would work, I needed to run this:

在此之前，我需要运行以下代码:

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

....to update the sys.servers table. (i.e. Using a self-reference within OPENQUERY seems to be disabled by default.)

....更新系统。服务器表。(例如，在OPENQUERY内使用自引用似乎在默认情况下被禁用。)

For my simple requirement, I ran into none of the problems described in the OPENQUERY section of Lance's excellent link.

对于我的简单需求，我没有遇到Lance's excellent link的OPENQUERY部分所描述的任何问题。

Rossini, if you need to dynamically set those input parameters, then use of OPENQUERY becomes a little more fiddly:

罗西尼，如果你需要动态设置这些输入参数，那么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);

I'm not sure of the differences (if any) between using sp_serveroption to update the existing sys.servers self-reference directly, vs. using sp_addlinkedserver (as described in Lance's link) to create a duplicate/alias.

我不确定使用sp_serveroption更新现有sys之间的差异(如果有的话)。服务器直接自我引用，而不是使用sp_addlinkedserver(如Lance的链接所述)创建一个重复/别名。

Note 1: I prefer OPENQUERY over OPENROWSET, given that OPENQUERY does not require the connection-string definition within the proc.

注意1:与OPENROWSET相比，我更喜欢OPENQUERY，因为OPENQUERY不需要proc中的连接字符串定义。

Note 2: Having said all this: normally I would just use INSERT ... EXEC :) Yes, it's 10 mins extra typing, but if I can help it, I prefer not to jigger around with:
(a) quotes within quotes within quotes, and
(b) sys tables, and/or sneaky self-referencing Linked Server setups (i.e. for these, I need to plead my case to our all-powerful DBAs :)

注意2:说了这么多:通常我只使用INSERT…EXEC:)是的,10分钟额外的输入,但如果我可以帮助它,我不喜欢周围的小玩意:(a)引用在引号内,和(b)系统表,和/或卑鄙的自参照链接服务器设置(即对这些,我需要为我的情况我们全能的dba:)

However in this instance, I couldn't use a INSERT ... EXEC construct, as sp_help_job is already using one. ("An INSERT EXEC statement cannot be nested.")

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

#5

To achieve this, first you create a #test_table like below:

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

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

Now execute procedure and put value in #test_table:

现在执行过程并在#test_table中输入值:

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

Now you fetch the value from #test_table:

现在从#test_table获取值:

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

#6

If you are able to modify your stored procedure, you can easily put the required columns definitions as a parameter and use an auto-created temporary 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

In this case you don't need to create a temp table manually - it is created automatically. Hope this helps.

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

#7

It might be helpful to know why this is so difficult. A stored procedure may only return text (print 'text'), or may return multiple tables, or may return no tables at all.

知道这为什么如此困难可能会有帮助。存储过程可能只返回文本(打印“text”)，也可能返回多个表，或者可能根本不返回任何表。

So something like SELECT * FROM (exec sp_tables) Table1 will not work

因此，类似SELECT * FROM (exec sp_tables) Table1这样的东西不能工作

#8

(Assuming SQL Server)

(假设SQL Server)

The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO ... EXEC syntax. That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need.

处理T-SQL存储过程的结果的惟一方法是使用INSERT to…EXEC语法。您可以选择插入到临时表或表变量中，然后从中选择所需的数据。

#9

A quick hack would be to add a new parameter '@Column_Name' and have the calling function define the column name to be retrieved. In the return part of your sproc, you would have if/else statements and return only the specified column, or if empty - return all.

一个快速的技巧是添加一个新的参数“@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

#10

If you're doing this for manual validation of the data, you can do this with LINQPad.

如果要手动验证数据，可以使用LINQPad实现。

Create a connection to the database in LinqPad then create C# statements similar to the following:

在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();

Reference http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet.aspx

参考http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet.aspx

#11

For SQL Server, I find that this works fine:

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

Create a temp table (or permanent table, doesn't really matter), and do a insert into statement against the stored procedure. The result set of the SP should match the columns in your table, otherwise you'll get an error.

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

Here's an example:

这里有一个例子:

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;

That's it!

就是这样!

#12

try this

试试这个

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

#13

As it's been mentioned in the question, it's hard to define the 80 column temp table before executing the stored procedure.

正如问题中提到的，在执行存储过程之前，很难定义80列临时表。

So the other way around this is to populate the table based on the stored procedure result set.

另一种方法是基于存储过程结果集填充表。

SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;'
                                   ,'EXEC MyStoredProc')

If you are getting any error, you need to enable ad hoc distributed queries by executing following query.

如果您有任何错误，您需要通过执行以下查询来启用特定的分布式查询。

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission

要使用两个参数执行sp_configure以更改配置选项或运行RECONFIGURE语句，必须授予ALTER SETTINGS服务器级权限

Now you can select your specific columns from the generated table

现在可以从生成的表中选择特定的列

SELECT col1, col2
FROM #temp

#14

Have you tried this:

你有试过这样的:

create a function which should call this StoredProcedure
创建一个应该调用StoredProcedure的函数
use a cursor to store the results returned from that StoredProcedure
使用游标存储从存储过程返回的结果
loop that cursor,So that you could print/store those two columns you need in to temporary table(with 2columns only)
循环游标，以便您可以将需要的那两列打印/存储到临时表中(只有两列)
and then use that table to select the results
然后使用该表选择结果

#15

-2

I'd cut and paste the original SP and delete all columns except the 2 you want. Or. I'd bring the result set back, map it to a proper business object, then LINQ out the two columns.

我将剪切粘贴原始SP，删除除了你想要的2列之外的所有列。或。我将返回设置好的结果，将其映射到适当的业务对象，然后将这两列进行LINQ。

#16

-4

Easiest way to do if you only need to this once:

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

Export to excel in Import and Export wizard and then import this excel into a table.

导出到导入和导出向导中的excel，然后将这个excel导入到一个表中。

#1

151