How to store a list in SQL?

1

Hello

I want to specify WHERE col1 is in (1,2,3).
However, I have a long list of 1,2,3... and I use it in multiple queries so I want to store it as a variable.

I tried:
DECLARE @list varchar = 1,2,3
or
DECLARE @list varchar = '1','2','3'

I checked a variable type matrix and couldn't find an applicable variable type.

Any idea? I would prefer not to create it as a table as it is only a list and I want to use it as a list and not a table.

2

You need to use a table, not least because SQL is optimized to use tables, not lists.

1 Like
3

No sure what version of sql you are using, but you could store them as XML or JSON. Not really sure what you are using them for, so, as always, it depends

4

as @ScottPletcher mentioned use tables but if you cannot use the following

DECLARE @list varchar(10) = '1,2,3'

declare @sql4 table(horsename varchar(150), COL1 int)
insert into @sql4
select distinct top 10 name, column_id from sys.all_columns

select horsename, COL1, b.*
  from @sql4 as a
  join dbo.DelimitedSplit8K(@list,',') as b on a.COL1 = b.ItemNumber

@JeffModen is author of DelimitedSplit8K. Search it online

You should also look into table types

CREATE TYPE UT_Employee AS TABLE  
(  
Emp_Id int NOT NULL
)
1 Like
5

Thanks, is there a way to do something like:

DECLARE @Table1 tablevariable = (1,2,3)

6

what is that? doesn't make any sense. now you just seem to be trolling

7

Yes - you can create a table variable. It would be a good idea to check the documentation first - but here you go:

Declare @table1 Table (col1 int);
 Insert Into @table1 (col1) Values (1), (2), (3);

Now - you can use that as you would any other table...this is the same as I showed you in your other question. To use it:

Select *
  From dbo.SomeTable st
 Where st.SomeColumn In (Select t.Col1 From @table1 t);