I have a nvarchar value something like '1,3,5,6', in my database table
But I want to use this in a where condition, something like this where event_id IN (1,3,5,6)
How do I achieve that, How can I convert it and use in the IN of a query
2
-
You need to use dynamic SQL to achieve this. The good news is, there's no harm in this if you follow best practice and check for SQL injection. Good luck!– user3914Apr 17, 2012 at 23:14
-
csv data in individual table cells in evil. fix your schema.– Joel CoelApr 18, 2012 at 17:09
Add a comment
|
2 Answers
This must be done via dynamic SQL. Something like this.
DECLARE @val nvarchar(20), @sql nvarchar(100)
set @val = (select SomeColumn from SomeTable)
SET @sql = 'SELECT * FROM AnotherTable WHERE Column3 IN (' + @val + ')'
exec (@sql)
-
Thank you for the details but, I need to use a little complicated query in the SET @sql section and it is not allowing me to execute It is giving the following error -'The multi-part identifier "a.aa" could not be bound.' and so on for each column in the select statement, please advice– TripzApr 18, 2012 at 14:36
-
Excellent. Don't forget to mark answers as correct on here as it'll help your reputation on the site.– mrdennyApr 18, 2012 at 16:58
I am able to fig-out and fix it not sure why but the issue is
The query is not executing if I write
SET @sql =
but its working when I change to
EXEC ('SELECT TOP 20 a.aa, a.bb, a.cc, a.dd, c.pp, a.ee FROM a LEFT OUTER JOIN b ON a.ff= b.ff LEFT OUTER JOIN c ON a.gg= c.gg WHERE len(a.aa) > 0 AND b.aa IN ('+@val +') AND a.xx= 1')
Thank you