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?

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