update stock by storedprocedure

i have a stored procedure which is basically updating my STOCK table when i sale item . which is as follows

ALTER PROCEDURE [dbo].[sp_UpdateStockForSale1]

@prodName varchar(40),

@stqty numeric(9,0),

@batchno varchar(40),

@IsSample varchar(5),

@S_n int

BEGIN

SET NOCOUNT ON;

DECLARE @S_en int;

DECLARE @ttavail numeric(9,0);

DECLARE @ttsold numeric(9,0);

While (@stqty > 0) BEGIN

Select @S_en=S_en,@ttavail=S_P_ttavail, @ttsold=S_P_ttsold From STOCK WHERE S_en=@S_n AND S_P_ttavail>0 AND S_P_name = @prodName AND S_P_batchno=@batchno And IsSample=@IsSample Order By S_en DESC;

--If Sale Qty is more than Stock

IF (@ttavail <= @stqty) BEGIN

SET @stqty = @stqty - @ttavail;

SET @ttsold=@ttsold + @ttavail;

SET @ttavail = 0;

END

--If Sale Qty is less than STOCK

ELSE IF(@stqty < @ttavail) BEGIN

SET @ttsold = @ttsold + @stqty

SET @ttavail = @ttavail - @stqty;

SET @stqty = 0;

END

Update STOCK Set S_P_ttavail = @ttavail, S_P_ttsold=@ttsold Where S_en=@S_en And IsSample=@IsSample

END

now I have to update my Sale bill , n so I can edit my sold qty i.e. stqty , how can I update STOCK tables now with this new updated stqty. how can i do this. can any1 help me.thanks

3 Replies

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

Ivan Lobo11y ago
Not sure but you can do this with either one 1. update-select statement or 2. using MERGE INTO YourTable T USING ( SELECT id, col1, col2 FROM other_table WHERE tsql = 'cool' ) S ON T.id = S.id WHEN MATCHED THEN UPDATE SET col1 = S.col1, col2 = S.col2; or 3. Use a trigger on the table -- Trigger check out update trigger on google
varsha dodiya11y ago
STOCK table

Product purchase sold available

vicks 20 5 15

now i change my sold qty to 3 in update,then it should be

Product purchase sold available

vicks 20 3 17
Joginder Banger11y ago
Hi
varsha,
plz share your stock and sale table design for better understanding.