April 18, 2012 at 9:59 am
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
I tried something like this but, I need to use a little complicated query in the SET @sql section and it is not allowing me to execute the below
DECLARE @val nvarchar(20), @sql nvarchar(100)
SET @val = '1,3,5,6'
SET @sql = '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'
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
April 18, 2012 at 10:51 am
You are on the right track with using dynamic sql to deal with the IN portion of the query.
The error you're receiving means the engine cannot find a column you're referencing. Since I cannot see your schema there is not much I can do for you, other than to ask for table definitions for a, b and c so we can see where your dynamic query goes awry.
As an aside, is b.aa a numeric-typed column?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 18, 2012 at 11:03 am
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
April 18, 2012 at 11:04 am
Another debugging tip, select your sql before you execute it so you can tell what it is you are going to run.
SELECT @sql
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 11:05 am
No problem...happy you got it sorted.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply