Assign a list of integers to an @var

Question

I can:

declare @idOrder int
set @idOrder = 21319

I want:

declare @idOrder int
set @idOrder = (21319, 21320)

for use in a series of statements where the 'WHERE' clause uses the IN operator

delete Orders where idOrder in @idOrder

instead of

delete Orders where idOrder in (21319, 21320)

Answer 1

You can't do that as long as it's an int, as that's not a valid value for that datatype. A datatype that could take several integers is a table

declare @idOrder table (id int)

insert into @idOrder values(21319)
insert into @idOrder values(21320)

delete from Orders where idOrder in (select id from @idOrder)

Answer 2

In MSSQL You can also

CREATE FUNCTION [dbo].[fn_ado_param_int] (@ado nvarchar(4000))
RETURNS @VALUES TABLE (ado int)AS
   BEGIN
   declare @Delim char(1)
   set @Delim = ','
   DECLARE @chrind INT
   DECLARE @Piece nvarchar(4000)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@ado)
         IF @chrind > 0
            SELECT @Piece = LEFT(@ado,@chrind - 1)
         ELSE
            SELECT @Piece = @ado
         INSERT @VALUES(ado) VALUES(@Piece)
         SELECT @ado = RIGHT(@ado,LEN(@ado) - @chrind)
         IF LEN(@ado) = 0 BREAK
      END
   RETURN
END

declare @idOrder varchar(500);
set @inOrder = "21319,2138,2138";
delete from Orders where id in (select ado from dbo.fn_ado_param_int(@idOrder));