sql stored procedure conditional

varsha dodiya

12y1.9k

Hi everyone ,

i m deducting some qty on basis of two conditions here ,these conditions are in loop.

ALTER PROCEDURE sp_UpdateStockForSale
@prodName varchar(40),
@stqty numeric(9,0),
@batchno varchar(40)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @S_en int;
DECLARE @savail Numeric(9,0);
DECLARE @ttavail numeric(9,0);
DECLARE @ttsold numeric(9,0);

While (@stqty > 0) BEGIN           (<- This is saying till sell qty does not become 0 )

Select @S_en=S_en,@ttavail=S_P_ttavail From STOCK Where S_P_ttavail > 0 And S_P_name = @prodName AND S_P_batchno=@batchno Order By S_en;

--If Sale Qty is more than Stock
IF (@ttavail < @stqty) BEGIN            (<- This is saying if avail qty is less than sale qty )
SET @stqty = @stqty - @ttavail;
SET @ttsold=@ttavail;
SET @ttavail = 0;

END
--If Sale Qty is less than STOCK
ELSE BEGIN                                           (<- This is saying if sale qty is less than avail qty )
SET @ttavail = @ttavail - @stqty;

SET @ttsold=@stqty
SET @stqty = 0;
END
Update STOCK Set S_P_ttavail = @ttavail, S_P_ttsold=@ttsold Where S_en=@S_en
END
END

______________________________________________

but i want to add one more condition here loop should continue deduction from avail qty row by row but when the last row of table comes is deduct remaining sale qty from the avail qty of that last row (if the last row is also not having sufficient qty or 0 avail qty then result should come in minus (-).)

help me in this, how to get row number if that row is last row from result of all rows in select statement

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ken H12y ago
Hi varsha,
Try your keyword substitution to the 'your_primary_key '
varsha dodiya12y ago
@ Ken H , thanks for rplying @S_en is my primary key
Ken H12y ago
Please try:

ALTER PROCEDURE sp_UpdateStockForSale
@prodName varchar(40),
@stqty numeric(9,0),
@batchno varchar(40)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @S_en int;
DECLARE @savail Numeric(9,0);
DECLARE @ttavail numeric(9,0);
DECLARE @ttsold numeric(9,0);
DECLARE @your_primary_key INT;

IF(@stqty<=0 OR EXISTS(SELECT SUM(S_P_ttavail) FROM STOCK WHERE S_P_name=@prodName HAVING SUM(S_P_ttavail)<@stqty)) RETURN 0

DECLARE stock_cur CURSOR FOR
SELECT your_primary_key ,S_en,S_P_ttavail FROM STOCK WHERE S_P_name = @prodName ORDER BY S_P_ttavail ASC
OPEN stock_cur
FETCH NEXT FROM stock_cur INTO @your_primary_key ,@S_en,@ttavail
WHILE((@@FETCH_STATUS=0 AND @stqty>0))
BEGIN
IF (@ttavail < @stqty)
BEGIN
SET @stqty = @stqty - @ttavail;
SET @ttsold=@ttavail;
SET @ttavail = 0;
END
ELSE
BEGIN
SET @ttavail = @ttavail - @stqty;
SET @ttsold=@stqty
SET @stqty = 0;
END
Update STOCK Set S_P_ttavail = @ttavail, S_P_ttsold=@ttsold Where S_en=@S_en AND your_primary_key =@your_primary_key
FETCH NEXT FROM stock_cur INTO @your_primary_key,@S_en,@ttavail
END
CLOSE stock_cur
DEALLOCATE stock_cur
END
varsha dodiya12y ago
@Ramesh , plz take a look , i have edited last table in my reply to make u understand it
varsha dodiya12y ago
yes but @ttsold is the is qty deducted from that row and @stqty is sale qty.
Ramesh Maruthi12y ago
you were saying if the total sale qty is more than the total available than we have to display the minus value for example 9-10=-1
varsha dodiya12y ago
WHEN @ttsold > @ttavail ??? y this condition , can u explain ? @ramesh
Ramesh Maruthi12y ago
ALTER PROCEDURE sp_UpdateStockForSale
@prodName varchar(40),
@stqty numeric(9,0),
@batchno varchar(40)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @S_en int;
DECLARE @savail Numeric(9,0);
DECLARE @ttavail numeric(9,0);
DECLARE @ttsold numeric(9,0);

Select @stqty,

CASE
WHEN @stqty > 0 THEN
Select @S_en=S_en,@ttavail=S_P_ttavail From STOCK Where S_P_ttavail > 0 And S_P_name = @prodName AND S_P_batchno=@batchno Order By S_en ;
WHEN @ttavail < @stqty THEN
SET @stqty = @stqty - @ttavail;
SET @ttsold=@ttavail;
SET @ttavail = 0;
WHEN @stqty < @ttavail
SET @ttavail = @ttavail - @stqty;
SET @ttsold=@stqty
SET @stqty = 0;
WHEN @ttsold > @ttavail
SET @ttavail = @ttavail - @ttsold;
END

don't know whether this works or not

varsha dodiya12y ago

basically i have 2 tables STOCK and BILL , in my BILL form i want to have total qty of particular product and its batch no. , for ex. i want to sale crocin of batch no c66 then in my STOCK table i have many rows of this product with its batch no. each row has its available qty .

crocin	c66	4
crocin	c5	5
crocin	c66	9

now here i have 2 rows of crocin -c66. so i'll have total available qty 13. now i may sale tablets in 3 case= if
1) i sale 12 from 13 available tablets so that (loop starts and deduct tablets first from 1st row (12-4), make it 0 then to 2nd row(9-8),make it 1).
2) I sale 13 tablets so 0 remain (then deduct from available amount row wise and make them 0)
3) I sale 14 tablets which is greater than total available (now i have to sale one more than total available , so loop should starts deduct from available amount else if last row comes then it should deduct amount from that row even if result goes in minus.)
like this
if i sale 14 tablets then @stqty=14)

crocin	c66	14-4=0 (@stqty-@ttavail)=@stqty , set @ttavail=0;
crocin	c5	5 (batchno is different ,condition false ,leave this row)
crocin	c66	9-10=-1 (because it is last row) (now here @ttavail=9 and remained @stqty =10 , and this is last row so deduct @ttavail=@ttavail-@stqty ,so @ttavail=-1, set stqty=0) loop ends

if u still have not got it ask me again . :)

Khan Abrar Ahmed12y ago
HI can you please explain more and please provide the example data.