如何在另一个内部使用存储过程的结果？

I have a stored procedure that I want to call from within another, and then loop through the results. Sort of like using a cursor with a stored procedure rather than a SQL select statement. I can't quite figure out how to do it.

我有一个存储过程，我想从另一个内部调用，然后遍历结果。有点像使用带有存储过程的游标而不是SQL select语句。我无法弄明白该怎么做。

I can get the whole result like this:

我可以像这样得到整个结果：

DECLARE @result int;
EXEC @result = sp_who;
PRINT @result;

Interestingly, this seems to change the type of @result to something other than int, but whatever. How do I then loop through the results, row by row? How do access the data from the individual columns? For example, how would I kill processes where the forth column (loginname) is like '%gatesb' or whatever?

有趣的是，这似乎将@result的类型改为int以外的东西，但无论如何。然后，我如何逐行循环结果？如何访问各列的数据？例如，我如何杀死第四列（loginname）就像'％gatesb'或其他什么的进程？

4 个解决方案

#1

You would declare a table variable to hold the results of the stored procedure and then loop through them in a while loop:

您将声明一个表变量来保存存储过程的结果，然后在while循环中循环它们：

declare @temp table (
    idx int identity(1,1),
    field1 int,
    field2 varchar(max))

declare @result int

insert into @temp (field1, field2)
exec @result = sp_who

declare @counter int

set @counter = 1

while @counter < (select max(idx) from @temp)
begin
    -- do what you want with the rows here
    set @counter = @counter + 1
end

#2

you can catch the results of a stored proc by inserting into a table that has matching columns...

您可以通过插入具有匹配列的表来捕获存储过程的结果...

create table #spWhoResults
    (spid smallint,
    ecid smallint,
    status nchar(60),
    loginame nchar(256),
    hostname nchar(256),
    blk char(5),
    dbname nvarchar(128),
    cmd nchar(32),
    request_id int)

go

insert  #spWhoResults
exec    sp_who


select  *
from    #spWhoResults

/* 
put your cursor here to loop #spWhoResults to 
perform whatever it is you wanted to do per row
*/

#3

Rewrite sp_who as a table function

将sp_who重写为表函数

#4

What Justin pointed out is what you have to do, but instead of doing

Justin指出的是你必须做的，而不是做

while @counter < (select max(idx) from @temp)

do this

做这个

declare @maxid int
select @maxid = max(idx), @counter = 1
from @temp
while @counter < @maxid begin
-- go on
  set @counter = @counter + 1
end

Also, if declaring the table as @temp doesn't work you could declare it as #temp.

此外，如果将表声明为@temp不起作用，则可以将其声明为#temp。

#1