Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
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! – Randolph West Apr 17 '12 at 23:14
csv data in individual table cells in evil. fix your schema. – Joel Coel Apr 18 '12 at 17:09

2 Answers

up vote 1 down vote accepted

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)
share|improve this answer
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 – Tripz Apr 18 '12 at 14:36
Excellent. Don't forget to mark answers as correct on here as it'll help your reputation on the site. – mrdenny♦ Apr 18 '12 at 16:58
Marked as correct. Thank You once again :-) – Tripz Apr 18 '12 at 17:05
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.