Is outputting to both a result set and a temporary table possible?

Question

I have a stored procedure that I need to both output the result set to the user and use a piece of information in the result set to possibly output other result sets.

Here is a basic schema of what I have

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)

Here is a very simplified version of the stored procedures.

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

And the first procedure is called like the following

update top 10000 Foo set IsQueued = 1
exec GetFoos

I am about to modify a large number of the stored procedures so it does not hit the Foo table twice to something like the following

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

Would this be the correct approach to doing this or is there a better way to both return the resultset (without BarId included) and update the 2nd table based on the returned rows?

• Anup Shah: you will not need second Select statement after insert. check the OUTPUT CLAUSE. technet.microsoft.com/en-us/library/ms177564(v=sql.90).aspx

Answer 1

you can alter the column in select or insert list as you need.

 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

Answer 2

Not looked calmly yet, but depending on the situation, this is a good way to do what you need.

But as a suggestion to make this way will help to optimize the process and review your data model to better serve tasks like this.

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

• Scott Chamberlain: Your change changes the output, FooId on FooQueue is not constrained to be unique so your version will now cause duplicate records to be returned. The select would need to be select distinct FooId, FooData from #t to get the same behavior as my original query.