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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy