February 2, 2010 at 9:34 am
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.
February 2, 2010 at 9:47 am
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
February 2, 2010 at 10:03 am
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