CODEWITHSUNDEEP
sqlsql-server
Jacek
Jacek

Reputation: 12063

Int array and IN statement

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

Upvotes: 2

Views: 24411

Answers (3)

GarethD
GarethD

Reputation: 69829

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;

Upvotes: 2

Guffa
Guffa

Reputation: 700910

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)

Upvotes: 4

podiluska
podiluska

Reputation: 51514

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.

Upvotes: 8

Related Questions