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 WHEN (@AS22_RightOfPledgeNum <> '') THEN
AND (SP.Data.value('(/*/Answers/AnswersList/Entry[@key="as22_RightOfPledgeNum"]/value)[1]', 'nvarchar(max)')= @AS22_RightOfPledgeNum) ELSE 1
END
对不起。下面的第一个答案不太正确。发布后我立即意识到我误读了查询。您需要基于变量的不同 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 条件添加到 JOIN 条件,以将数据拉取限制为感兴趣的行,然后将所需的 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;