In your C# code, the variable
C
is scoped within the
while
loop. Each time the loop executes, the variable will be reset to its default value of
null
.
In SQL, the variable is scoped to the batch. It will retain its value from the previous execution of the loop. When the
SELECT
statement doesn't match any rows, the value of the variable will
not be updated.
To solve the problem, reset the variable before the
SELECT
statement:
WHILE @W > 0
BEGIN
SET @C = Null;
SELECT @C = Rec_No FROM PaymentDetails WHERE FY_Code = @FYCode And Rec_No = @w;
If @C Is Not Null
BEGIN
SET @w = @w + 1;
END
Else
BEGIN
SET @RecNo = @w;
BREAK;
END;
END;
As I mentioned in the comments, your C# code is vulnerable to
SQL Injection[
^]. You need to get rid of the string concatenation and use a parameterized query instead.