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

podiluska · Accepted Answer · 2012-11-22 08:44:32Z

8

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.

answered Nov 22, 2012 at 8:44

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guffa · Accepted Answer · 2012-11-22 08:46:03Z

4

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)

answered Nov 22, 2012 at 8:46

Guffa

703k111 gold badges760 silver badges1k bronze badges

1 Comment

GarethD Over a year ago

You can't use value to add multiple records?? Perhaps not in earlier versions of SQL-Server, but it has been a feature since at least 2008.

GarethD · Accepted Answer · 2012-11-22 08:46:07Z

2

To switch statements you need to use IF rather than CASE, you also need to put each value to insert in it's own parentheses.

e.g.

DECLARE @mode INT = 1;
DECLARE @acceptedFormTypeIds TABLE (id INT);

IF @mode = 1
    INSERT @acceptedFormTypeIds (id) VALUES (1), (2), (3), (4);
ELSE IF @mode = 2
    INSERT @acceptedFormTypeIds (id) VALUES (1), (3);
ELSE IF @mode = 2
    INSERT @acceptedFormTypeIds (id) VALUES (2), (4);
ELSE 
    INSERT @acceptedFormTypeIds (id) VALUES (1), (2), (3), (4);


SELECT  *
FROM    @acceptedFormTypeIds;

answered Nov 22, 2012 at 8:46

GarethD

70k11 gold badges90 silver badges130 bronze badges

Collectives™ on Stack Overflow

Int array and IN statement

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related