February 11, 2012 at 5:08 am
Hi,
I am passing the following string to my stored procedure
declare @string nvarchar(8000)
@string = 'abc,deg,hij'
set @string= +'''' + replace (@string, ',', ''',''') + ''''
select @string
i need to format this string and use it in my sql IN statement ie
select * from Alpha where alpha in (@string)
When i run the sql i get no values retured.Is it possible to use a REPLACE like this to format my string for the IN part of my statement?.I need a quick solution and would prefer not to use a split string function.
Thanks
J
February 11, 2012 at 5:25 am
Place it in dynamic query. It won’t require split function.
set @SQL = 'select * from Alpha where alpha in(' + @strIN + ')'
February 11, 2012 at 7:29 am
I would strongly recommend a split function. Dynamic SQL has a number of downsides in this situation - plan caching and SQL injection risks been the two main ones.
Regardless, you need either a split or dynamic SQL with the appropriate checks and verification or some other similar trick. If you pass a variable/parameter to an IN, SQL treats it like a single literal value, not a list.
So, if we have this
DECLARE @SomeDelimitedString varchar(50) = '1,2,3,4,5'
SELECT * FROM SomeTable where AColumn IN (@SomeDelimitedString)
SQL considers that identical to this
DECLARE @SomeDelimitedString varchar(50) = '1,2,3,4,5'
SELECT * FROM SomeTable where AColumn = @SomeDelimitedString
Which I'm sure is not what you want to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply