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
AS
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
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