Pass a variable for IN statement 1

[kennedydottie]

Hi... I have a Stored procedure where I'm receiving a few values as parameters, one of which I am using for an IN statement

@myvariable
note: @myvariable will contain several items and needs to be formatted accordingly, which I knkow how to do this, but the sp doesn't appear to be accepting the variable and using it as I expected.

select * from mytable
where mycol in (@myvariable)

Is this even possible?
Is this possible?

 

[gmmastros]

faq183-5207

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Geoka]

You can achieve this by Dynamic SQL, here is one small sample.

Filter condition applied on String field

declare @MyVar  varchar(100)
declare @newvar  varchar(1500)
set @MyVar = '~155A06420~,~151A06420~,~515A06607~'
set @newvar = 'select * from MyTable where MyCol in(' + replace(@MyVar,'~','''') + ')'
exec (@newvar)

Filter condition applied on Numeric field

declare @MyVar  varchar(100)
declare @newvar  varchar(1500)
set @MyVar = '4,6,7'
set @newvar = 'select * from MyTable where MyCol in(' + @MyVar + ')'
exec (@newvar)

 

[markros]

In SQL Server 2008 you can pass table with the values to the SP directly.

Another alternative would be to use XML for the parameter.