TSQL table variable initialization

Question

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 1

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 

SQL Fiddle Demo

Answer 2

The following does not address the OP's question of initializing a table. You are welcome to treat it as a formatted comment.

A trick that is handy for the odd lookup table is to create a virtual table on the fly:

declare @Foo as Table ( Month Int )
insert into @Foo values ( 1 ), ( 3 ), ( 9 )

select *
  from @Foo as F inner join
    ( select month_id, num_days
      from ( values
      ( 1, 31 ), ( 2, 28 ), ( 3, 31 ), ( 4, 30 ), ( 5, 31 ), ( 6, 30 ),
      ( 7, 31 ), ( 8, 31 ), ( 9, 30 ), ( 10, 31 ), ( 11, 30 ), ( 12, 31 )
      ) as NumDaysMonth( month_id, num_days ) ) as NumDaysMonth on
    NumDaysMonth.month_id = F.Month

For getting the number of days in a month I would be more inclined to create a function that takes the year and month and returns the correct value. When I need a quick one off translation from some code to something readable the un-table is convenient.

If you need to refer to the faux table a few times in one place:

; with NumDaysMonth as (
  ( select month_id, num_days
    from ( values
      ( 1, 31 ), ( 2, 28 ), ( 3, 31 ), ( 4, 30 ), ( 5, 31 ), ( 6, 30 ),
      ( 7, 31 ), ( 8, 31 ), ( 9, 30 ), ( 10, 31 ), ( 11, 30 ), ( 12, 31 )
      ) as NumDaysMonth( month_id, num_days ) ) ),
  FooMonths as (
    select *
      from @Foo as F inner join
        NumDaysMonth as NDM on NDM.month_id = F.Month ),
  FooWithFollowingMonths as (
    select *
      from FooMonths
    union
    select *
      from @Foo as F inner join
        NumDaysMonth as NDM on NDM.month_id = F.Month + 1 )
  select *
    from FooWithFollowingMonths

Beyond that the lookup table should probably be kept as a real table or table valued function.