问如何在where子句中检查COndition
EN

Stack Overflow用户

提问于 2018-03-05 11:56:43

回答 1查看 633关注 0票数 0

SELCT * FROM @Locations Loc
    INNER JOIN SubProposal SP ON SP.MasterProposalId = Loc.ProposalId
    WHERE SP.EndEffectiveDate = @EndEffectiveDate
    AND ISNULL(SP.Data.value('(/*/IsRemovedByEndorsement)[1]', 'bit'), 0) = 0
    CASE WHEN (@AS22_RightOfPledgeNum <> '') THEN
    AND (SP.Data.value('(/*/Answers/AnswersList/Entry[@key="as22_RightOfPledgeNum"]/value)[1]', 'nvarchar(max)')= @AS22_RightOfPledgeNum) ELSE 1 
    END

它在case条件附近抛出错误：“CASE附近的语法不正确

我需要如何检查上述条件?只有在以下情况下才需要包含此语句

 CASE WHEN (@AS22_RightOfPledgeNum <> '') THEN
    AND (SP.Data.value('(/*/Answers/AnswersList/Entry[@key="as22_RightOfPledgeNum"]/value)[1]', 'nvarchar(max)')= @AS22_RightOfPledgeNum) ELSE 1 
    END

sql-server

sql-server-2008

case

where-clause

回答 1

Stack Overflow用户

发布于 2018-03-05 12:29:23

抱歉的。下面的第一个答案并不完全正确。就在我发帖之后，我意识到我误解了这个查询。您希望根据变量使用不同的WHERE子句。因此，您需要在查询之外使用IF语句，因为SQL无法理解您试图使用它的方式。我不太明白你在这里想做什么。您的WHERE语句需要有一个用于测试的条件。如果我理解你的意思，

IF @AS22_RightOfPledgeNum <> ''
BEGIN
        SELECT * FROM @Locations Loc
        INNER JOIN SubProposal SP 
            ON SP.MasterProposalId = Loc.ProposalId
        WHERE SP.EndEffectiveDate = @EndEffectiveDate
            AND ISNULL(SP.Data.value('(/*/IsRemovedByEndorsement)[1]', 'bit'), 0) = 0
            AND (SP.Data.value('(/*/Answers/AnswersList/Entry[@key="as22_RightOfPledgeNum"]/value)[1]', 'nvarchar(max)')= @AS22_RightOfPledgeNum) 
ELSE
        SELECT * FROM @Locations Loc
        INNER JOIN SubProposal SP 
            ON SP.MasterProposalId = Loc.ProposalId
        WHERE SP.EndEffectiveDate = @EndEffectiveDate
            AND ISNULL(SP.Data.value('(/*/IsRemovedByEndorsement)[1]', 'bit'), 0) = 0;

旧的答案(不完全是在金钱上)：您正在尝试基于行中的数据应用不同的WHERE子句。不过，WHERE子句适用于整个数据集。您需要将数据分成两个集合，并将正确的WHERE子句分别应用于每个集合。

将CASE条件添加到连接条件，以将数据提取限制到您感兴趣的行，然后将WHERE条件应用于每个集合。

如下所示：

SELCT * FROM @Locations Loc
    INNER JOIN SubProposal SP 
        ON SP.MasterProposalId = Loc.ProposalId
        AND @AS22_RightOfPledgeNum <> ''

        WHERE SP.EndEffectiveDate = @EndEffectiveDate
    AND ISNULL(SP.Data.value('(/*/IsRemovedByEndorsement)[1]', 'bit'), 0) = 0
    AND (SP.Data.value('(/*/Answers/AnswersList/Entry[@key="as22_RightOfPledgeNum"]/value)[1]', 'nvarchar(max)')= @AS22_RightOfPledgeNum) 
UNION ALL
SELCT * FROM @Locations Loc
    INNER JOIN SubProposal SP 
        ON SP.MasterProposalId = Loc.ProposalId
        AND @AS22_RightOfPledgeNum = ''

    WHERE SP.EndEffectiveDate = @EndEffectiveDate
    AND ISNULL(SP.Data.value('(/*/IsRemovedByEndorsement)[1]', 'bit'), 0) = 0;