TSQL table variable initialization

sql tsql lookup-tables table-variable

CodeKingPlusPlus · Sep 25, 2012 · Viewed 25.5k times · Source

I have the following TSQL table variable:

declare @NumDaysMonth table
(
   month_id smallint,
   num_days smallint
)

I just want a quick look-up for the number of days in each month. How can I initialize this table like a C array:

int numDaysMonth[] = {31, 28, 30, ... , 31};

Answer

Well you can't. The best you can do is something like this

Insert Into @NumDaysMonth
Values 
(1,31),
(2,28),
(3,31),
...
(12,31);

Then retrieval might be something like

DECLARE @LookItUp int

SELECT @LookItUp = num_days 
FROM @NumDaysMonth
WHERE month_Id = 12;

PRINT @LookItUp