在 WHERE 子句中使用可选参数 [英] use optional parameter in WHERE clause

问题描述

我有一个SP

ALTER PROCEDURE dbo.sp_Compare
    @lst varchar(100),
    @frst varchar(100) = NULL,
    @passportNo varchar(50) = NULL
AS
SELECT * FROM dbo.User
WHERE LastName like '%' + @lst + '%'
    AND (
         FirstName like @frst + ' %'
         OR FirstName like '% ' + @frst + ' %'
         OR FirstName like '% ' + @frst
         OR FirstName = @frst
        )
    OR Passport = @passportNo;

有时@frs 或@passportNo 或两者的详细信息可能不可用

Sometimes @frs or @passportNo or both the details may not be available

所以我想更改上面的查询，以便在未传递可选参数时(即，当其值为空时)不应考虑对该特定列进行过滤

so I want to change the above query such that when optional parameter is not passed(ie., when its value is null) that parameter shouldn't be considered for filtering on that particular column

即，如果 @frst 为 NULL，则结果不应基于 FirstName 进行过滤，它应该像没有 FirstName 一样工作，如 '%' + @frst + 'WHERE 子句中的 %' 条件..

viz., if @frst is NULL then result shouldn't be filtered based in FirstName, it should work as if there was no FirstName like '%' + @frst + '%' condition in WHERE clause..

如何在不针对不同情况重复编写相同逻辑的情况下编写查询?

How can I write the query without writing the same logic repeatedly for different cases?

推荐答案

WHERE (@frst IS NULL OR 
    (
         LastName like '%' + @lst + '%'
         AND (
             FirstName like @frst + ' %'
             OR FirstName like '% ' + @frst + ' %'
             OR FirstName like '% ' + @frst
             OR FirstName = @frst
        )
    )
 )
 AND (@passportNo IS NULL OR
        Passport = @passportNo);

编辑:实际上这应该更有效:

Edit: Actually this should be more efficient:

WHERE ( @frst IS NULL OR FirstName like '%' + @frst + '%' )
AND   ( @lst  IS NULL OR LastName  like '%' + @lst  + '%' )
AND   ( @passportNo IS NULL OR Passport = @passportNo )

这篇关于在 WHERE 子句中使用可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！