loop in Stored Procedure

See more:

SQL-Server

SQL-Server-2008

I have table called SpDet having pcode(product code),qty(quantity),vtype(voucher type sale/purchase) ,spdt(transaction date)rate ,amt etc.Now the problem is i have find the closing stock of all products between a specific date.Though i have done it for single item but i can not do it for all products can you all help me !!!!

Sql Stored Procedure

create procedure [dbo].[stocktrial3] @dtfrom date,@dtto date

as

begin

SET NOCOUNT ON ;

declare @inqty int,@outqty int,@opinqty int,@opoutqty int,@opqty int,@opclqty int,@clqty int

select @opqty=opqty from Item order by PCode

select @opinqty = SUM(qty) from SPDet where Vtype='PU' and SPDt < @dtfrom  group by PCode,Vtype order by PCode

select @opoutqty =SUM(qty) from SPDet where Vtype in ('SP','CM') and SPDt < @dtfrom  group by PCode,Vtype order by PCode

select @inqty =SUM(qty) from SPDet where Vtype='PU' and SPDt between @dtfrom and @dtto  group by PCode,Vtype order by PCode

select @outqty =SUM(qty) from SPDet where Vtype in ('SP','CM') and SPDt between @dtfrom and @dtto  group by PCode,Vtype order by PCode

set @opclqty=@opqty+ISNULL(@opinqty,0)-ISNULL(@opoutqty,0)

set @clqty=ISNULL(@opclqty,0)+ISNULL(@inqty,0)-ISNULL(@outqty,0)

select Item.Pname,@opclqty as PopQty,ISNULL(@inqty,0) as PinQty,ISNULL(@outqty,0) as POutQty,@clqty as PClqty

 from Item order by PCode

end

Result

PName   Op.Qty  InQty  OutQty   Clqty
VAIO    20  0   5   15
Lenevo Laptop   20  0   5   15
LCD 32" 20  0   5   15
LED 32" 20  0   5   15
SS  20  0   5   15
DD  20  0   5   15
Pendrive    20  0   5   15
Raid & Tailor   20  0   5   15
Laptop  20  0   5   15
Tab 20  0   5   15
Lenevo Desktop  20  0   5   15

As u can see that result is all product is same.