是否可以同時輸出到結果集和臨時表？

我有一個儲存過程，我既需要將結果集輸出給使用者，又需要使用結果集中的一條資訊來可能輸出其他結果集。

這是我所擁有的基本架構

create table FooQueue(FooId int, IsQueued bit)
create table Foo(FooId int primary key, BarId int, FooData varchar(50))

create table BarQueue(BarId int, IsQueued bit)
create table Bar(BarId int primary key, BarData varchar(50)

這是儲存過程的一個非常簡化的版本。

create procedure GetFoos as
begin
   select FooId, FooData 
   from Foo 
   where FooId in (select FooId from FooQueue where IsQueued = 1)

   update BarQueue 
   set IsQueued = 1 
   where BarId in (select BarId 
                   from Foo 
                   where FooId in (select FooId from FooQueue where  IsQueued = 1))

   exec GetBars

   delete FooQueue where IsQueued = 1
end
GO

create procedure GetBars as
begin
   select BarId, BarData 
   from Bar 
   where BarId in (select BarId from BarQueue where IsQueued = 1)

   delete Bar where IsQueued = 1
end
GO

第一個過程的呼叫如下

update top 10000 Foo set IsQueued = 1
exec GetFoos

我即將修改大量儲存過程，使其不會Foo兩次命中表，如下所示

alter procedure GetFoos as
begin
   select FooId, BarId, FooData 
   from Foo
   into #t
   where FooId in (select FooId from FooQueue where IsQueued = 1)

   select FooId, FooData from #t

   update BarQueue 
   set IsQueued = 1 
   where BarId in (select BarId from #t)

   exec GetBars

   delete FooQueue where IsQueued = 1
end
GO

這是否是正確的方法，還是有更好的方法來返回結果集（不BarId包括在內）並根據返回的行更新第二個表？

您可以根據需要更改選擇或插入列表中的列。

   alter procedure GetFoos as
   BEGIN
       CREATE TABLE #t (FooId INT, BarId INT, FooData SYSNAME)

       select FooId, BarId, FooData 
       OUTPUT INSERTED.FooId, INSERTED.BarId,INSERTED.FooData INTO #t(FooId, BarId, FooData )
       from Foo
       where FooId in (select FooId from FooQueue where IsQueued = 1)

       update BarQueue 
       set IsQueued = 1 
       where BarId in (select BarId from #t)

       exec GetBars

       delete FooQueue where IsQueued = 1
   end
   GO

還沒有平靜地看著，但根據情況，這是做你需要的好方法。

但建議採用這種方式將有助於優化流程並審查您的數據模型以更好地服務於此類任務。

alter procedure GetFoos 
as 

begin

select t1.FooId, t1.BarId, t1.FooData 
from Foo t1 , FooQueue t2
into #t
where t1.FooId = t2.FooId
and t2.IsQueued = 1

select FooId, FooData from #t

update BarQueue 
set IsQueued = 1 
where BarId exists (select BarId from #t)

exec GetBars

delete FooQueue where IsQueued = 1
end
GO

引用自：https://dba.stackexchange.com/questions/64160