Insert multiple records with INSERT and CASE 1

[cwsstins]

SQL Server 2005, trying to insert multiple records into table variable using one insert statement (no problem) and a CASE statement (problem). Here is a snippet:

...
DECLARE @YrTbl TABLE
(Yr VARCHAR(7))

SET @TodayMn = MONTH(getdate())
SET @TodayYr = YEAR(getdate())
SET @2Years = YEAR(getdate())-2
SET @LastYr = YEAR(getdate())-1

SET @Yr1 = @2Years + ‘-’ + RIGHT(@LastYr,2) -- ’2009-10?
SET @Yr2 = @LastYr + ‘-’ + RIGHT(@TodayYr,2) -- ’2010-11?
SET @AwdYr3 = @TodayYr + '-' + RIGHT(@NextYr,2) -- '2011-12'

-- I know the syntax is not correct, but this is what I want to be able to do:
INSERT INTO @YrTbl SELECT CASE 
WHEN @TodayMn in (1) THEN SELECT @Yr1 UNION ALL SELECT @Yr2 
WHEN @TodayMn in (2,3) THEN SELECT @Yr2 UNION ALL SELECT @Yr3
end

SELECT Yr from @YrTbl

What I’m trying to get from my SELECT:
2009-10
2010-11

If I do this without the CASE, it's no problem:
INSERT INTO @YrTbl SELECT @Yr1 UNION ALL SELECT @Yr2
SELECT Yr from @YrTbl

How can I incorporate the CASE in order to select based on month? Ultimate goal is to be able to re-use the main part of the script for many different queries where the SELECT Yr can be driven by the month you're in when you run it.

 

[gmmastros]

Case is used to control data. If can be used to control logic. As such, I would encourage you to try this:

-- I know the syntax is not correct, but this is what I want to be able to do:
IF @TodayMn In (1)
  INSERT INTO @YrTbl 
  SELECT @Yr1 UNION ALL SELECT @Yr2 
Else If @TodayMn In (2,3)
  INSERT INTO @YrTbl 
  SELECT @Yr2 UNION ALL SELECT @Yr3

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cwsstins]

Perfect, thank you!