Sql中While循环的Sintex中的更正 [英] Correction in Sintex of While loop in sql

问题描述

亲爱的先生，

虽然C＃中的Loop工作正常，但在SQL中却没有。请检查...

C＃中的代码：

Dear Sir,
While Loop in C# is working properly, but in SQL is not. Please check it ...
Code in C# :

int w = crno + 1;
                while (w>0)
                {
                    string C = AccessUnique("select " + ColSr + " from " + TName + " where " + ColSr + "='" + w.ToString() + "' AND FY_Code='" + FY + "'");
                    if (C != null)
                    {
                        w++;
                    }
                    else if (C == null)
                    {
                        CRNO = Convert.ToString(w);
                        break;
                    }
                }

SQL语法：

Syntax in SQL :

set @w = @RecNoN;
    while (@w>0)
        begin
            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 if (@C is null)
                begin
                    set @RecNo = @w
                    BREAK;
                end;
            BREAK;
        end;

推荐答案

在您的C＃代码中，变量 C 的范围是，而循环。每次循环执行时，变量将重置为其默认值 null 。



在SQL中，变量作用于批处理。它将保留其先前执行循环的值。当 SELECT 语句与任何行都不匹配时，变量的值将不更新。



要解决此问题，请在 SELECT 语句之前重置变量：

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 -- No need to test here - if the code gets here, @C is null by definition
    BEGIN
        SET @RecNo = @w;
        BREAK;
    END;
END;

正如我在评论中提到的，您的C＃代码容易受到 SQL Injection [< a href =http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html\"target =_ blank> ^ ]。您需要摆脱字符串连接并使用参数化查询。

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.

这篇关于Sql中While循环的Sintex中的更正的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！