SQL Server中有多个独立的IF条件

发布时间：2020-12-12 16:46:50 所属栏目：MsSql教程来源：网络整理

导读：我有多个IF语句在我的存储过程中是彼此独立的.但是由于某些原因,它们被嵌套在一起,就像它们是一个大的if语句的一部分一样 ELSE IF(SOMETHNGZ) BEGIN IF(SOMETHINGY) BEGIN..END ELSE IF (SOMETHINGY) BEGIN..END ELSE BEGIN..END --The above works I then in

我有多个IF语句在我的存储过程中是彼此独立的.但是由于某些原因,它们被嵌套在一起,就像它们是一个大的if语句的一部分一样

ELSE IF(SOMETHNGZ)
 BEGIN
  IF(SOMETHINGY)
   BEGIN..END
  ELSE IF (SOMETHINGY)
   BEGIN..END
  ELSE
   BEGIN..END
  --The above works I then insert this below and these if statement become nested----
  IF(@A!= @SA)

  IF(@S!= @SS)

  IF(@C!= @SC) 

  IF(@W!= @SW)
  --Inserted if statement stop here
 END
ELSE <-- final else

所以会这样对待

IF(@A!= @SA){           
        IF(@S!= @SS){           
            IF(@C!= @SC) {      
                IF(@W!= @SW){}
            }
        }
    }

我期望是这样的

IF(@A!= @SA){}          
IF(@S!= @SS){}      
IF(@C!= @SC){}
IF(@W!= @SW){}

我也试过这个,它在“ELSE”附近抛出不正确的语法.期待“对话”

IF(@A!= @SA)
BEGIN..END                  
IF(@S!= @SS)
BEGIN..END      
IF(@C!= @SC) 
BEGIN..END  
IF(@W!= @SW)
   BEGIN..END

注意,从ELSE < - final else down现在嵌套在IF(@W！= @SW)内即使它是外部if语句ELSE IF(SOMETHNGZ)之前的一部分. 编辑根据要求我的完整声明

ALTER Procedure [dbo].[SP_PLaces]  
@ID int,..more params
AS
BEGIN
SET NOCOUNT ON
DECLARE @SomeId INT
..more varaible
SET @SomeId = user define function()
..more SETS
IF(@ID IS NULL)
BEGIN
BEGIN TRY
    INSERT INTO Places              
    VAlUES(..Values...)            
    ... more stuff...               
    BEGIN TRY       
        exec Store procedure 
            @FIELD = 15,... more params...             
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS 'Message' 
        RETURN -1
    END CATCH                      
    RETURN 0                
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS 'Message' 
    RETURN -1
END CATCH   
END 
ELSE IF(@ID IS NOT NULL AND @ID in (SELECT ID FROM Places)) 
BEGIN   
     SELECT @MyName = Name ...  
    ...Some stuff....                       
    IF(SOMETHNG_1)          
        BEGIN TRY               
            UPDATE ....                                                                 
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS 'Message' 
            RETURN -1
        END CATCH
    ELSE IF(SOMETHNG_2)
        BEGIN TRY
            UPDATE ...                                                      
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS 'Message' 
            RETURN -1
        END CATCH   
    ELSE  
        BEGIN
            BEGIN TRY
                UPDATE ...                                                              
            END TRY
            BEGIN CATCH
                SELECT ERROR_MESSAGE() AS 'Message' 
                RETURN -1
            END CATCH   
        END             
      --The above works I then insert this below and these if statement become nested----
  IF(@A!= @SA)
    BEGIN
     exec Stored procedure 
            @FIELD = 15,... more params...
    END                 
IF(@S!= @SS)
  BEGIN
     exec Stored procedure 
            @FIELD = 10,... more params...
    END     
IF(@C!= @SC) 
  BEGIN
     exec Stored procedure 
            @FIELD = 17,... more params...
    END 
IF(@W!= @SW)
    BEGIN
     exec Stored procedure 
            @FIELD = 12,... more params...
    END
  --Inserted if statement stop here             
END     
ELSE    
    BEGIN
        SET @ResultMessage = 'Update/Delete Failed. No record found with   ID:'+CONVERT(varchar(50),@ID) 
        SELECT @ResultMessage AS 'Message' 
        RETURN -1
    END
Set NOCOUNT OFF
END

解决方法

如果你根据多个条件检查一个变量,那么你会使用这样的东西
这里将执行条件为true的代码块,其他块将被忽略.

IF(@Var1 Condition1)
     BEGIN
      /*Your Code Goes here*/
     END

ELSE IF(@Var1 Condition2)
      BEGIN
        /*Your Code Goes here*/ 
      END 

    ELSE      --<--- Default Task if none of the above is true
     BEGIN
       /*Your Code Goes here*/
     END

如果您正在检查多个变量的条件,那么您将不得不去多个IF
语句,每个代码块将独立于其他块执行.

IF(@Var1 Condition1)
 BEGIN
   /*Your Code Goes here*/
 END


IF(@Var2 Condition1)
 BEGIN
   /*Your Code Goes here*/
 END


IF(@Var3 Condition1)
 BEGIN
   /*Your Code Goes here*/
 END

在每个IF语句之后,如果执行了多个语句,则必须将它们放入
BEGIN..END块.无论如何,总是最好的做法是使用BEGIN..END块

更新

在你的代码中找到一些你缺少的BEGIN END

ELSE IF(@ID IS NOT NULL AND @ID in (SELECT ID FROM Places))   -- Outer Most Block ELSE IF
BEGIN   
     SELECT @MyName = Name ...  
    ...Some stuff....                       
    IF(SOMETHNG_1)         -- IF
                 --BEGIN
        BEGIN TRY               
            UPDATE ....                                                                 
        END TRY

        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS 'Message' 
            RETURN -1
        END CATCH
                -- END
    ELSE IF(SOMETHNG_2)    -- ELSE IF
                 -- BEGIN
        BEGIN TRY
            UPDATE ...                                                      
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS 'Message' 
            RETURN -1
        END CATCH   
               -- END
    ELSE                  -- ELSE
        BEGIN
            BEGIN TRY
                UPDATE ...                                                              
            END TRY
            BEGIN CATCH
                SELECT ERROR_MESSAGE() AS 'Message' 
                RETURN -1
            END CATCH   
         END             
      --The above works I then insert this below and these if statement become nested----
          IF(@A!= @SA)
            BEGIN
             exec Store procedure 
                    @FIELD = 15,... more params...
            END                 
        IF(@S!= @SS)
          BEGIN
             exec Store procedure 
                    @FIELD = 10,... more params...

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!