Increment each row according to condition in SQL C#

I have a value of 100 from a textBox and I want to increment that value in the stock_out column but only till stock_out column is equal to stock_in column, if first row value is 40 than first row stock_out should be incremented by 10 from 100 than the remaining value from 100(90) should be incremented in the row below it and then row below it till needed (till 100 is 0). Items are ordered by Item_Name and Bill No asc.

Here is the code that I have been able to do so far, but it needs a lot of work and I do not have expertise in cte or any complicated c# coding. I am using SQL Server 2014 and c#.

C# code SQL Query

select * from stock_recieve  where Item_Name like'" + comboBoxitem.Text + "' where Stock_out < Stock_in  order by [Bill No] asc;  update stock_recieve       set Stock_out = Stock_out+" + qty + " where Stock_out < Stock_in;

Any help would be greatly appreciated.

P.S I know about the sql injection and I will change my sql queries to parameterized queries.

Try with this

--------------------------------------

DECLARE @stock_recieve AS TABLE

(

Bill_No INT IDENTITY(1, 1),

Item_Name NVARCHAR(100),

Stock_In INT,

Stock_Out INT,

Stock_In_Hand INT

)

INSERT INTO @stock_recieve

VALUES

('chicken', 50, 40, 10)

INSERT INTO @stock_recieve

VALUES

('chicken', 40, 0, 40)

INSERT INTO @stock_recieve

VALUES

('fish', 100, 30, 70)

INSERT INTO @stock_recieve

VALUES

('fish', 20, 20, 0)

INSERT INTO @stock_recieve

VALUES

('chicken', 60, 10, 50)

INSERT INTO @stock_recieve

VALUES

('chicken', 100, 20, 80)

-----------------------------------------------

DECLARE @lItemName NVARCHAR(100)

DECLARE @lStockRequired INT

DECLARE @lLoopTotalCount INT

DECLARE @lLoopCount INT

DECLARE @lLoopStockInHand INT

DECLARE @lAvailQty INT

DECLARE @tempStockAvailQty AS TABLE

(

ID INT IDENTITY(1, 1),

Bill_No INT,

Stock_In_Hand INT,

Stock_Out INT

)

SELECT @lItemName = 'chicken',

@lStockRequired = 100

SELECT @lAvailQty = SUM(Stock_In_Hand)

FROM @stock_recieve

WHERE Item_Name = @lItemName

AND Stock_In_Hand <> 0

IF (@lAvailQty >= @lStockRequired)

BEGIN

INSERT INTO @tempStockAvailQty

(

Bill_No,

Stock_In_Hand

)

SELECT Bill_No,

Stock_In_Hand

FROM @stock_recieve

WHERE Item_Name = @lItemName

AND Stock_In_Hand <> 0

SELECT @lLoopTotalCount = Count(ID),

@lLoopCount = 1

FROM @tempStockAvailQty

WHILE @lLoopCount <= @lLoopTotalCount

BEGIN

SELECT @lLoopStockInHand = Stock_In_Hand

FROM @tempStockAvailQty

WHERE ID = @lLoopCount

SELECT @lStockRequired = @lStockRequired - @lLoopStockInHand

IF @lStockRequired < 0

BEGIN

DELETE FROM @tempStockAvailQty

WHERE ID > @lLoopCount

BREAK

END

ELSE

BEGIN

UPDATE @tempStockAvailQty

SET Stock_In_Hand = Stock_In_Hand - @lLoopStockInHand,

Stock_Out = @lLoopStockInHand

WHERE ID = @lLoopCount

END

SELECT @lLoopCount = @lLoopCount + 1

END

UPDATE S

SET S.Stock_In_Hand = T.Stock_In_Hand,

S.Stock_Out = S.Stock_Out + ISNULL(T.Stock_Out, 0)

FROM @stock_recieve S

INNER JOIN @tempStockAvailQty T

ON S.Bill_No = T.Bill_No

SELECT result = 1,

result_msg = 'Save Successfully'

END

ELSE

BEGIN

SELECT result = 2,

result_msg = 'Qty Not Available'

END

------------------------

**Note- Try to used SQL Procedure

1 Reply

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

sudipta sanyal8y ago
Try with this
--------------------------------------
DECLARE @stock_recieve AS TABLE
(
Bill_No INT IDENTITY(1, 1),
Item_Name NVARCHAR(100),
Stock_In INT,
Stock_Out INT,
Stock_In_Hand INT
)
INSERT INTO @stock_recieve
VALUES
('chicken', 50, 40, 10)
INSERT INTO @stock_recieve
VALUES
('chicken', 40, 0, 40)
INSERT INTO @stock_recieve
VALUES
('fish', 100, 30, 70)
INSERT INTO @stock_recieve
VALUES
('fish', 20, 20, 0)
INSERT INTO @stock_recieve
VALUES
('chicken', 60, 10, 50)
INSERT INTO @stock_recieve
VALUES
('chicken', 100, 20, 80)
-----------------------------------------------
DECLARE @lItemName NVARCHAR(100)
DECLARE @lStockRequired INT
DECLARE @lLoopTotalCount INT
DECLARE @lLoopCount INT
DECLARE @lLoopStockInHand INT
DECLARE @lAvailQty INT
DECLARE @tempStockAvailQty AS TABLE
(
ID INT IDENTITY(1, 1),
Bill_No INT,
Stock_In_Hand INT,
Stock_Out INT
)
SELECT @lItemName = 'chicken',
@lStockRequired = 100
SELECT @lAvailQty = SUM(Stock_In_Hand)
FROM @stock_recieve
WHERE Item_Name = @lItemName
AND Stock_In_Hand <> 0
IF (@lAvailQty >= @lStockRequired)
BEGIN
INSERT INTO @tempStockAvailQty
(
Bill_No,
Stock_In_Hand
)
SELECT Bill_No,
Stock_In_Hand
FROM @stock_recieve
WHERE Item_Name = @lItemName
AND Stock_In_Hand <> 0
SELECT @lLoopTotalCount = Count(ID),
@lLoopCount = 1
FROM @tempStockAvailQty
WHILE @lLoopCount <= @lLoopTotalCount
BEGIN
SELECT @lLoopStockInHand = Stock_In_Hand
FROM @tempStockAvailQty
WHERE ID = @lLoopCount
SELECT @lStockRequired = @lStockRequired - @lLoopStockInHand
IF @lStockRequired < 0
BEGIN
DELETE FROM @tempStockAvailQty
WHERE ID > @lLoopCount
BREAK
END
ELSE
BEGIN
UPDATE @tempStockAvailQty
SET Stock_In_Hand = Stock_In_Hand - @lLoopStockInHand,
Stock_Out = @lLoopStockInHand
WHERE ID = @lLoopCount
END
SELECT @lLoopCount = @lLoopCount + 1
END
UPDATE S
SET S.Stock_In_Hand = T.Stock_In_Hand,
S.Stock_Out = S.Stock_Out + ISNULL(T.Stock_Out, 0)
FROM @stock_recieve S
INNER JOIN @tempStockAvailQty T
ON S.Bill_No = T.Bill_No

SELECT result = 1,
result_msg = 'Save Successfully'
END
ELSE
BEGIN
SELECT result = 2,
result_msg = 'Qty Not Available'
END
------------------------
**Note- Try to used SQL Procedure