Skip IF statements

• texpic

  SSCertifiable

  Points: 5885

  More actions

  October 12, 2006 at 3:56 am

  #169982

  How would I skip IF statements once one was executed.  Here is a simple exampe:

  IF MyField = 1 BEGIN

  TRUNCATE TABLE AAA

  --Goto Finish

  END

  IF MyField = 2 BEGIN

  TRUNCATE TABLE BBB

  --Goto Finish

  END

  IF MyField = 3 BEGIN

  TRUNCATE TABLE CCC

  --Goto Finish

  END

  EXEC p_Finish

   

• Mark Harr

  SSCrazy Eights

  Points: 9921

  More actions

  October 12, 2006 at 6:11 am

  #665444

  To directly answer your question, nest them.

  IF MyField = 1 
    BEGIN
      TRUNCATE TABLE AAA
    END
  ELSE
    BEGIN
      IF MyField = 2 
        BEGIN
          TRUNCATE TABLE BBB
        END
      ELSE
        BEGIN
          IF MyField = 3 
            BEGIN
              TRUNCATE TABLE CCC
            END
        END
     END

  I put plenty of BEGIN..END statements in here to show the levels, in case you have more code to put in there.  Of course, if table truncation is all you need, you can eliminate all those:

  IF MyField = 1
    TRUNCATE TABLE AAA
  ELSE
    IF MyField = 2
      TRUNCATE TABLE BBB
    ELSE
      TRUNCATE TABLE CCC

  But even easier in this case would be using dynamic SQL:

  DECLARE @SQL nvarchar(200)
  SET @SQL = N'TRUNCATE TABLE '

  SELECT @SQL = @SQL + CASE MyField
    WHEN 1 THEN N'AAA'
    WHEN 2 THEN N'BBB'
    WHEN 3 THEN N'CCC' END

  sp_executesql @SQL

  While dynamic SQL is generally not a good idea, this would not be bad.  I seriously doubt there is little plan cacheing for a truncate table statement, that would have any performance benefit.

  Hope this helps

  
  
  Mark