Int array and IN statement

Question

How can I declare int array in SQL Server?

After fill it, I would like to use it in IN statement to check, how can I change my code?

declare @mode int =1 
declare @acceptedFormTypeIds table (id int)

case @mode
    when 1 then
        insert into @acceptedFormTypeIds(id) values (1, 2, 3, 4)
    when 2 then 
        insert into @acceptedFormTypeIds(id) values (1, 3)
    when 3 then 
        insert into @acceptedFormTypeIds(id) values (2, 4)
    else
        insert into @acceptedFormTypeIds(id) values (1, 2, 3, 4)
end

...

WHERE
    tFRM.SendDate between @datefrom and @dateto
    and tFRM.TemplateId IN @acceptedFormTypeIds.id

Answer 1

In databases, don't ever think of arrays. Think of sets, or tables.

The structure of the insert statement should be

insert into @acceptedFormTypeIds(id) values (1), (2), (3), (4)

You can use if

if @mode=2
begin
   insert into @acceptedFormTypeIds(id) values (1), (3)
end
else
begin
    if @mode=3
    begin
       insert into @acceptedFormTypeIds(id) values (2), (4)
    end
    else
    begin
       insert into @acceptedFormTypeIds(id) values (1), (2), (3), (4)
    end
end

However, it may be worth having a FormTypeModeAccepted table depending on your usage.

Answer 2

There is no switch-like statement, the case is an expression, so you would use if.

To insert multiple records, you use values (2), (4) instead of values (2, 4).

When using the table in in, you need to select from it.

declare @mode int = 1
declare @acceptedFormTypeIds table (id int)

if (@mode = 1) begin
  insert into @acceptedFormTypeIds(id) values (1), (2), (3), (4)
end else if (@mode = 2) begin 
  insert into @acceptedFormTypeIds(id) values (1), (3)
end else if (@mode = 3) begin 
  insert into @acceptedFormTypeIds(id) values (2), (4)
end else begin
  insert into @acceptedFormTypeIds(id) values (1), (2), (3), (4)
end

...    
WHERE
    tFRM.SendDate between @datefrom and @dateto
    and tFRM.TemplateId IN (select id from @acceptedFormTypeIds)