Reputation: 12063
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
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
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
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