.netsql-serversql-server-2008stored-proceduresclrstoredprocedure

Stored procedure, storing results in another table before returning to client


A stored procedure is using below query to fetch and return results to client.

select 
    @Lid, *         
from 
    CurrentProductSet cps 
where 
    cps.State = @state
    and cps.ProductName in (select gbb.ProductName 
                            from HMCGoodBetterBest gbb 
                            where gbb.HMC_Hospital = @hospital 
                              and gbb.HMC_Extras = @extra);

Can you please guide me how I can store these results in anther table for further use before returning them to client. Just don't want to fetch data twice or use a table variable. I have created another table 'Temp_CurrentProductSet'.

Edit:

I tried using into clause trying below code but I get this error:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Code:

select 
    @Lid, * 
into 
    Temp_CurrentProductSet
from 
    CurrentProductSet cps 
where 
    cps.State = @state
    and cps.ProductName in (select gbb.ProductName 
                            from HMCGoodBetterBest gbb 
                            where gbb.HMC_Hospital = @hospital 
                              and gbb.HMC_Extras = @extra);

Solution

  • **You need to use output clause**
    
    insert into Temp_CurrentProductSet output Inserted.*
    select 
    @Lid, *         
    from 
    CurrentProductSet cps 
    where 
    cps.State = @state
    and cps.ProductName in (select gbb.ProductName 
                            from HMCGoodBetterBest gbb 
                            where gbb.HMC_Hospital = @hospital 
                              and gbb.HMC_Extras = @extra);