variable value in in clause

Question

Post reply

variable value in in clause

mayank jarsaniya

Hall of Fame

Points: 3532
More actions
June 11, 2009 at 7:50 am

#222447

create table emp(id int identity,name varchar(20),surname varchar(20))
insert into emp values('may','nk7')
insert into emp values('may1','nk6')
insert into emp values('may2','nk5')
insert into emp values('may3','nk4')
declare @list nvarchar(max);
set @list='1,2,3,4'
select name,surname
from emp
where id in(@LIST)
I want to pass @list at runtime.
Is it possible.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could do it splitting the parameters into a table with a script like this:

http://www.sqlservercentral.com/scripts/T-SQL+Aids/31871/

There are lots around, even more efficient, with tally tables or with loops, just pick one.

create table emp(id int identity,name varchar(20),surname varchar(20))

insert into emp values('may','nk7')

insert into emp values('may1','nk6')

insert into emp values('may2','nk5')

insert into emp values('may3','nk4')

declare @list nvarchar(max);

set @list='1,2,3,4'

select name,surname

from emp

where id in(

SELECT Value

FROM [dbo].[fSplit](@list, ',')

)

Hope this helps

Gianluca

-- Gianluca Sartori

WayneS SSC Guru Points: 95461 More actions · Answer 2

mjarsaniya (6/11/2009)
select name,surname
from emp
where id in(@LIST)
I want to pass @list at runtime.
Is it possible.

I outline my favorite way in Using XML to Enhance the Performance of String Manipulations[/url].

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2