执行SQL Server存储过程

Question

我很困惑为什么这没有执行第二个else if语句。我只需要在没有提供First_name和姓氏但有年龄的情况下运行语句。它运行第一个if语句，但不运行第二部分。

以下是代码

ALTER PROCEDURE [PEOPLE]
(
    @AGE int,
    @SURNAME nvarchar(10),
    @FIRST_NAME nvarchar(10)
)
AS
BEGIN   
    IF(@SURNAME IS NOT NULL AND @FIRST_NAME IS NOT NULL)
        BEGIN
            SELECT * 
            FROM MEMBERS
            WHERE Is_Active = 1
            AND (FIRST_NAME = @FIRST_NAME AND SURNAME=@SURNAME)
        END     
        
        -- This else statement isn't being executed :-(
        ELSE IF(@AGE <> -1 AND @FIRST_NAME='' AND @SURNAME='')
            BEGIN
                SELECT *
                FROM MEMBERS
                WHERE(AGE = @AGE)
            END         
    END

下面是我是如何执行它的：

exec [PEOPLE] @AGE=24, @FIRST_NAME='', @SURNAME=''

如果有人能帮上忙，我将不胜感激。谢谢

Answer 1

ELSE IF要求@FIRST_NAME和@SURNAME都不能为null，这在IF中已经得到了满足。

您可能需要重写ELSE IF，如下所示：

 ELSE IF(@AGE <> -1 AND (@FIRST_NAME IS NULL OR @FIRST_NAME='') AND (@SURNAME IS NULL OR @SURNAME=''))

Answer 2

试试这个：

ALTER PROCEDURE [PEOPLE]
(
    @AGE int,
    @SURNAME nvarchar(10),
    @FIRST_NAME nvarchar(10)
)
AS
BEGIN   
  SELECT * 
  FROM MEMBERS
  WHERE
    (
       (IsNull(@FIRST_NAME, '') <> '') AND
       (IsNull(@SURNAME, '') <> '') AND
        FIRST_NAME = IsNull(@FIRST_NAME, '') AND
        SURNAME = IsNull(@SURNAME, '') AND
        Is_Active = 1
    )
    OR
    (
      @AGE <> -1 AND
      (IsNull(@FIRST_NAME, '') = '') AND
      (IsNull(@SURNAME, '') = '') AND
      (AGE = @AGE)
    )
END