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

您将声明一个表变量来保存存储过程的结果,然后在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

将sp\u who重写为表函数

您可以将存储过程的结果插入到具有匹配列的表中…

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
*/

贾斯汀指出的是你必须做的,但不是做

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

这样做

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

另外,如果将表声明为@temp不起作用,您可以将其声明为temp。