Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rama108
Posting Yak Master
115 Posts |
 Posted - 2012-06-18 : 12:33:39
|
| Hello All,I have a query where I need to use an "IN" clause like:AND e.Id IN (@EmpId)but it gives me the following errorConversion failed when converting the varchar value '162638,152866,147997,166881,166882,147979,137371,111381,150385,195020,156623,181321,88895,128844,104505,124932,105633,76845,107936,138734,137327,45637,174646,141378' to data type int.It is a huge query and I do not want to do a dynamic sql. What are my options?Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-18 : 12:37:17
|
| make where condition likeAND ',' + @EmpID + ',' LIKE '%,' + CAST(e.ID aS varchar(10)) + ',%'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
rama108
Posting Yak Master
115 Posts |
Posted - 2012-06-18 : 12:42:42
|
| Visakh, What you stated does not make sense. I am looking for:AND e.Id IN (@EmpId). The "IN" phrase. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-18 : 14:56:01
|
| you cant use variable with comma separated value inside IN to filter on the value list. you need to either use dynamic sql if you want to persist with IN or rewrite condition using LIKE as posted by me------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rama108
Posting Yak Master
115 Posts |
Posted - 2012-06-19 : 14:09:40
|
| WOW! Visakh. That seems to be working. Sorry that I doubted you without trying. Thanks you so much.Thanks again for your time. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-19 : 14:11:56
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Conversion failed when converting varchar