Use of Temp Table and Split Function

 Cursor in Sql

Store temporary data in Stored Procedure in SQL

 How to Retrieve Last Inserted Identity of Record

Split Function

Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))    
    returns @temptable TABLE (items varchar(8000))    
    as    
    begin    
        declare @idx int    
        declare @slice varchar(8000)    
       
        select @idx = 1    
            if len(@String)<1 or @String is null  return    
       
        while @idx!= 0    
        begin    
            set @idx = charindex(@Delimiter,@String)    
            if @idx!=0    
                set @slice = left(@String,@idx - 1)    
            else    
                set @slice = @String    
           
            if(len(@slice)>0)
                insert into @temptable(Items) values(@slice)    
   
            set @String = right(@String,len(@String) - @idx)    
            if len(@String) = 0 break    
        end
    return    
    end

Using this Split function in Stored Procedure with temp table

Create proc [dbo].[Dinner_SelectIngredientWeightByOrder]

@OrderIDs varchar(max)--Pass comma seperated Orderid

as

begin

declare @query varchar(max)

CREATE TABLE #tempWeight(

Weight decimal(18,4),

ItemId int,

ItemName varchar(100) )

DECLARE  @RecipeID int 

DECLARE Order_cursor CURSOR FOR 

Select Recipeid from dinner_orderdetails 

where  orderid in (

    select  * from dbo.split(@OrderIDs,',')  

)

OPEN Order_cursor;

FETCH NEXT FROM Order_cursor 

INTO @RecipeID;

WHILE @@FETCH_STATUS = 0

BEGIN

insert into #tempWeight

select Sum(weight) 'Weight',itemid,

(select itemname from inv where itemid=ri.itemid )as IngredientName

from recpinv ri

where recipeid =@RecipeID group by itemid

print '----------'

FETCH NEXT FROM Order_cursor 

    INTO @RecipeID;

END

CLOSE Order_cursor;

DEALLOCATE Order_cursor;

select Sum(Weight)as Weight,itemname from #tempWeight group by itemname

drop table #tempWeight

end