Using IF BEGIN ... END within an SQL statement?

  • What I am trying to do is based upon a parameter value to insert records into different tables.

    Given a parameter value @PeriodType (nvarchar(15)) that can be 'Monthly' or 'Daily' (and potentially other periods in the future).

    I want to be able to do the following:

    *******************

    IF @PeriodType = 'Monthly'

    BEGIN

    INSERT INTO Monthly_Summary_Data (Column_1, Column_2, etc.)

    END

    IF @PeriodType = 'Daily'

    BEGIN

    INSERT INTO Daily_Summary_Data (Column_1, Column_2, etc.)

    END

    SELECT (Value_1, Value_2, etc.)

    FROM SourceTable

    *******************

    Unfortunately I think that I am getting an error ("Incorrect syntax near the keyword 'END'") because the INSERT and SELECT cannot be separated (I.E. one of them within an IF and the other not) but if anyone knows how to do this I would really appreciate some help. I don't want to have to duplicate the code in the SELECT statement for each of the period types as this would be a pain to maintain.

    I guess I could create a table valued function to return the results of the Select statement but I would prefer to have all of the code within this single stored procedure if possible.

  • You can do that, but IF is control flow and not part of a SQL statement, hence you must put complete insert statements within the IF blocks

    IF @PeriodType = 'Monthly'

    BEGIN

    INSERT INTO Monthly_Summary_Data (Column_1, Column_2, etc.)

    SELECT (Value_1, Value_2, etc.)

    FROM SourceTable

    END

    IF @PeriodType = 'Daily'

    BEGIN

    INSERT INTO Daily_Summary_Data (Column_1, Column_2, etc.)

    SELECT (Value_1, Value_2, etc.)

    FROM SourceTable

    END

    I don't want to have to duplicate the code in the SELECT statement for each of the period types as this would be a pain to maintain.

    You'll have to, unless you want to do some really stupid stuff with dynamic SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • For the dynamic SQL approach:

    declare @sqlnvarchar(4000)

    set @sql =

    '

    INSERT INTO '+

    case

    when @PeriodType = 'Monthly'then N'Monthly_Summary_Data'

    when @PeriodType = 'Daily'then N'Daily_Summary_Data'

    else null end

    +' (Column_1, Column_2, etc.)

    SELECT

    Source_col_1,

    Source_col_2

    FROM

    SourceTable

    '

    exec sp_executesql @sql

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply