如何在另一个存储过程中使用存储过程的结果?

问题描述：

我有一个存储过程，我想从另一个内部调用它，然后遍历结果.有点像在存储过程中使用游标而不是 SQL 选择语句.我完全不知道该怎么做.

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.

我可以得到这样的整个结果:

I can get the whole result like this:

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

有趣的是，这似乎将@result 的类型更改为 int 以外的类型，但无论如何.然后我如何逐行循环结果?如何从各个列访问数据?例如，我将如何终止第四列(登录名)类似于%gatesb"或其他内容的进程?

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?

答

您可以声明一个表变量来保存存储过程的结果，然后在 while 循环中遍历它们:

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

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

如何在另一个存储过程中使用存储过程的结果?

相关推荐