lundi 25 février 2019

Nesting if-statements inside stored procedure

I'm creating a stored procedure which needs to check several conditions, and depending on the outcome has to execute specific queries. I have trouble when it comes to nested IF-statements, and I really can't seem to figure out what's wrong with the syntax.

Here's a snippet of my code (the rest is pretty much the same, but with other values)

 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

It keeps complaining about the second IF-statement after the first one (IF @Ugedagnr = '1').

How are nested IF-statements written?

Aucun commentaire:

Enregistrer un commentaire