Declared varchar in where condition

Question

I want to pass declared variables to where.

For example I have a table #test:

ID Amount
1  100
2  50
3  20
4  40 
5  150

I want to do something like that:

declare @id varchar(11) = '(1, 4, 5)'
select * from #test where IDNumber  in @id

How can I do that?

Answer 1

Create a table valued user defined function as given below. There are many examples available online.

Convert comma separated list to Table valued function

this will be giving you good results and better approach, than dynamic sql code.

Once you have the function dbo.split(@string NVARCHAR(4000),',') then use them in the JOIN as given below.

declare @id varchar(11) = '1, 4, 5'
select t.* from #test as t
JOIN dbo.Split(@id,',') AS csv
ON t.id = csv.data;