在存储过程的where子句中使用If

I am writing a stored procedure with 3 parameters and my where clause changes depending on one of these parameteres. Is it possible to write a SQL query in this way -

我正在编写一个包含3个参数和where子句的存储过程，这取决于其中的一个参数。是否可能以这种方式编写SQL查询

    CREATE PROCEDURE [dbo].[VendorVettingModal] @column NVarchar (50), @applicanttype NVarchar (10), @donotuse int AS

                declare @column NVarchar (50), @applicanttype NVarchar (10), @donotuse int

    select a.Id, a.Firstname, rs.Status,cs.ClearanceStatus
    from applicant a 
    left join ReviewStatus rs on a.ReviewStatus = rs.Id 
    left join ClearanceStatus cs on a.ClearanceStatus = cs.Id
    where
    if(@column = 'Recruiting')
    begin
        a.applicanttype = @applicanttype and a.reviewstatus = 7 and a.donotuse = @donotuse      
    end
    else if(@column = 'Clearance')
    begin
        a.applicanttype = @applicanttype and (a.reviewstatus != 7 or a.reviewstatus is null) and a.donotuse = @donotuse     
    end

Rather than writing this way? Because I have about 20-25 columns and a lot more joins and where params than defined here. I have just tried to make it less complicated here.

而不是这样写?因为我有大约20-25列和更多的连接以及比这里定义的params更多。我只是想让它不那么复杂。

    CREATE PROCEDURE [dbo].[VendorVettingModal] @column NVarchar (50), @applicanttype NVarchar (10), @donotuse int AS

                declare @column NVarchar (50), @applicanttype NVarchar (10), @donotuse int

    if(@column = 'Recruiting')
    begin
        select a.Id, a.Firstname, rs.Status,cs.ClearanceStatus
        from applicant a 
        left join ReviewStatus rs on a.ReviewStatus = rs.Id 
        left join ClearanceStatus cs on a.ClearanceStatus = cs.Id
        where
        a.applicanttype = @applicanttype and a.reviewstatus = 7 and a.donotuse = @donotuse      
    end
    else if(@column = 'Clearance')
    begin
        select a.Id, a.Firstname, rs.Status,cs.ClearanceStatus
        from applicant a 
        left join ReviewStatus rs on a.ReviewStatus = rs.Id 
        left join ClearanceStatus cs on a.ClearanceStatus = cs.Id
        where
        a.applicanttype = @applicanttype and (a.reviewstatus != 7 or a.reviewstatus is null) and a.donotuse = @donotuse     
    end

3 个解决方案

#1

Use parenthesis:

使用括号:

 select a.Id, a.Firstname, rs.Status,cs.ClearanceStatus
    from applicant a 
    left join ReviewStatus rs on a.ReviewStatus = rs.Id 
    left join ClearanceStatus cs on a.ClearanceStatus = cs.Id
    where a.applicanttype = @applicanttype
    and a.donotuse = @donotuse 
    AND ((@column = 'Recruiting' AND (a.reviewstatus = 7))
    OR
    (@column = 'Clearance' AND (a.reviewstatus != 7 or a.reviewstatus is null)))

#2

You can do this two ways. One way uses dynamic SQL. However, that is not generalizable to any database. The alternative is to structure the WHERE clause as:

你可以用两种方法。一种方法使用动态SQL。但是，这对任何数据库都不是通用的。另一种选择是将WHERE子句结构为:

where (case when @column = 'Recruiting' and
                 a.applicanttype = @applicanttype and a.reviewstatus = 7 and a.donotuse = @donotuse
            then 'True'
            when @column = 'Clearance' and
                 a.applicanttype = @applicanttype and (a.reviewstatus != 7 or a.reviewstatus is null) and a.donotuse = @donotuse
            then 'True'
            . . .
       end) = 'True'

Two advantages of this over dynamic SQL is that the query does not have to be recompiled and it works in a broader range of databases. One disadvantage is that the WHERE clause may not take advantage of applicable indexes.

与动态SQL相比，它的两个优点是查询不需要重新编译，并且可以在更广泛的数据库中工作。一个缺点是WHERE子句不能利用可适用的索引。

#3

-2

Unfortunalety, in transact-sql the "if-else-if-else" only can write this: http://msdn.microsoft.com/en-us/library/ms182587.aspx

不幸的是，在transact-sql中，“if-else-if-else”只能这样写:http://msdn.microsoft.com/en-us/library/ms182587.aspx

DECLARE @Number int;
SET @Number = 50;
IF @Number > 100
   PRINT 'The number is large.';
ELSE 
    BEGIN
        IF @Number < 10
            PRINT 'The number is small.';
        ELSE
            PRINT 'The number is medium.';
    END;
GO

It's complicated, but only this way is possible

这很复杂，但只有这样才有可能

#1