时间戳

首页 > 解决方案 > 在存储过程中嵌套 if 语句

sql-server - 在存储过程中嵌套 if 语句

问题描述

我正在创建一个需要检查多个条件的存储过程，并且根据结果必须执行特定的查询。当涉及到嵌套的 IF 语句时，我遇到了麻烦，而且我似乎真的无法弄清楚语法有什么问题。

这是我的代码片段（其余部分几乎相同，但具有其他值）

 IF @periodeKD = 'Formiddag'
  IF @Ugedagnr = '1'
        BEGIN TRANSACTION
                    @ModulID = 'S1'
                    @ModulID2 = 'S2'
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber)
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID2, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber) 
        COMMIT TRANSACTION
  IF @Ugedagnr = '2'
        BEGIN TRANSACTION
                    @ModulID = 'S5'
                    @ModulID2 = 'S6'
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber)
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID2, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber) 
        COMMIT TRANSACTION
  IF @Ugedagnr = '3'
        BEGIN TRANSACTION
                    @ModulID = 'S9'
                    @ModulID2 = 'S10'
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber)
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID2, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber) 
        COMMIT TRANSACTION
  IF @Ugedagnr = '4'
        BEGIN TRANSACTION
                    @ModulID = 'S13'
                    @ModulID2 = 'S14'
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber)
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID2, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber) 
        COMMIT TRANSACTION
  IF @Ugedagnr = '5'
        BEGIN TRANSACTION
                    @ModulID = 'S17'
                    @ModulID2 = 'S18'
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber)
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID2, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber) 
        COMMIT TRANSACTION

它一直在抱怨第一个（IF @Ugedagnr = '1'）之后的第二个 IF 语句。

嵌套的 IF 语句是如何编写的？

标签： sql-serverif-statementstored-procedures

解决方案

如果您的插入语句相同，则可以使用以下代码段：

DECLARE @Ugedagnr VARCHAR(10) = '1',
    @ModulID VARCHAR(10),
    @ModulID2 VARCHAR(10);

IF @Ugedagnr = '1'
BEGIN
    IF @Ugedagnr = '1'
    BEGIN
        SET @ModulID = 'S1';
        SET @ModulID2 = 'S2';
    END;

    IF @Ugedagnr = '2'
    BEGIN
        SET @ModulID = 'S5';
        SET @ModulID2 = 'S6';
    END;

    IF @Ugedagnr = '3'
    BEGIN
        SET @ModulID = 'S9';
        SET @ModulID2 = 'S10';
    END;
    IF @Ugedagnr = '4'
    BEGIN
        SET @ModulID = 'S13';
        SET @ModulID2 = 'S14';
    END;
    IF @Ugedagnr = '5'
    BEGIN
        SET @ModulID = 'S17';
        SET @ModulID2 = 'S18';
    END;

    SELECT @ModulID, @ModulID2


    BEGIN TRANSACTION
                    @ModulID = 'S9'
                    @ModulID2 = 'S10'
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber)
                    INSERT INTO Undervisning (ModulID, SkemaDato, LokaleID, HoldID, FagID, LaererID, Aar, Uge)
                    VALUES (@ModulID2, GETDATE(), @lokaleID, @holdID, @fagID, @laererID, @CurrentYear, @WeekNumber) 
    COMMIT TRANSACTION
END;

推荐阅读