在比较字符串和值时，适用于if语句的正确SQL语法

Question

我正在尝试更新存储在SQL Server 2012中的博客文章的评论和/或评级。

我需要检查注释是否为空字符串，如果是通过该代码块。

我需要检查额定值是否为0.0，如果是这样，则绕过该代码块。评论和评级使用令牌[Comment]，[RatingValue]。

注入SQL语句

上面的适当语法是什么？这是我尝试的：

IF [Comment] <> ""
    UPDATE EasyDNNNewsComments 
    SET Comment = '[Comment]'
    WHERE CommentID = [CommentID]
ELSE IF [NewRating] <> 0.0
    SELECT 
        EasyDNNNewsComments.Comment, EasyDNNNewsComments.DateAdded, 
        EasyDNNNewsComments.UserID, EasyDNNNewsComments.CommentID, 
        Users.UserName
    FROM 
        EasyDNNNewsComments
    INNER JOIN 
        Users ON EasyDNNNewsComments.UserID = Users.UserID
    WHERE 
        (ArticleID = [ArticleID])
    ORDER BY 
        EasyDNNNewsComments.CommentID DESC
UPDATE ArticleRating
SET RatingValue = [NewRating]
WHERE ArticleID = [ArticleID] AND UserID = [UserID]
UPDATE EasyDNNNews
SET RatingValue = (SELECT Sum(RatingValue) FROM ArticleRating WHERE ArticleID = [ArticleID]) / (SELECT Count(*) FROM ArticleRating WHERE ArticleID = [ArticleID])
SELECT RatingValue 
FROM EasyDNNNews 
WHERE ArticleID = [ArticleID]

Answer 1

IF [Comment] <> ""除非有 SET QUOTED_IDENTIFIER OFF

对于UPDATE EasyDNNNewsComments SET Comment = '[Comment]'，我假设您要将其设置为列值，而不是字符串文字，因此您要删除围绕[Comment]

Answer 2

我希望不使用标签"注入"代码中（除非在您的情况下严格必要），而是创建一个过程并传递参数。遵循一个原始的示例（可能有一些错误，因为我写了它而没有表格，也没有进行任何测试，但它应该给您一个想法）。

当然，您应该添加检查错误，检查参数和其他内容以使代码更强，更可靠。

CREATE PROCEDURE DO_IT 
     @pComment VARCHAR(500)
   , @pComment_id INT
   , @pNewRating NUMBER(10,2)
   , @pArticleID INT
   , @pUserID INT)
AS
BEGIN  
    SET NOCOUNT ON;
    IF @pComment <> '' 
        UPDATE EasyDNNNewsComments 
        SET Comment = @pComment
        WHERE CommentID = @pComment_id
    ELSE IF @pNewRating <> 0.0
        BEGIN
            SELECT EasyDNNNewsComments.Comment
                , EasyDNNNewsComments.DateAdded
                , EasyDNNNewsComments.UserID
                , EasyDNNNewsComments.CommentID
                , Users.UserName
            FROM EasyDNNNewsComments
            INNER JOIN Users ON EasyDNNNewsComments.UserID = Users.UserID
            WHERE ArticleID = @pArticleID
            ORDER BY EasyDNNNewsComments.CommentID DESC
            UPDATE ArticleRating SET RatingValue = @pNewRating
            WHERE ArticleID = @pArticleID AND UserID = @pUserID;
            UPDATE EasyDNNNews SET RatingValue = 
                               (SELECT Sum(RatingValue) /  Count(*)
                                FROM ArticleRating 
                                WHERE ArticleID = @pArticleID) ;
        END
        SELECT RatingValue 
        FROM EasyDNNNews 
        WHERE ArticleID = @pArticleID;
END