从存储过程的结果集中选择列 [英] Select columns from result set of stored procedure

问题描述

我有一个存储过程，该过程返回80列和300行.我想编写一个选择，以获取其中的2列.

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

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?

• 我可以制作一个临时表来放入结果，但是因为有80列，所以我需要制作一个80列的临时表才能得到2列.我想避免跟踪所有返回的列.

• 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.

我尝试按照Mark的建议使用WITH SprocResults AS ....，但出现2条错误

I tried using WITH SprocResults AS .... as suggested by Mark, but I got 2 errors

关键字'EXEC'附近的语法不正确.
')'附近的语法不正确.

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

如果我尝试
SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
我收到错误消息:

If I try
SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
I get the error :

关键字"exec"附近的语法不正确.

Incorrect syntax near the keyword 'exec'.

推荐答案

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

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.

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

SELECT col1, col2 FROM @tablevar

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