Where .. IN ...

[eyalle]

Hello all I have this query

DECLARE @a nvarchar(50)
SET @a = '1,3'

select *
from tableName
where CAST(ColumnId as nvarchar(50)) in (@a)

which doesn't work. i would like it to work like the following query

select *
from tableName
where ColumnId in (1,3)

please advice Thank's

[Vipin_Arora]

Hi, Try with two diffrenet integer parameters if your columnID is integer. its working fine 4 me..

Happy Coding... :)

[Mycroft Holmes]

You are trying to compare a string '1,3' with an integer field that is not going to work. Either do as Wipin suggested and use 2 variables or change the entire query to dynamic sql

DECLARE @a nvarchar(50)
SET @a = 'select * from tableName where ColumnId in (1,3)'
exec (@a)

Never underestimate the power of human stupidity RAH

[Blue_Boy]

You can try this:

DECLARE @a nvarchar(50)
SET @a = '1,3'

declare @tsql as nvarchar(max)
set @tsql = 'select *
from tableName
where CAST(ColumnId as nvarchar(50)) in ('+@a+')'

exec (@tsql)

---

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

[Lost User]

I'd advice to not use the "in" operator with a user-defined varchar, but to name the values individually.

SELECT *
FROM tableName
WHERE ColumnId = 1 OR ColumnId = 2

You can simply concatenate "OR ColumnId = @value" to the query and set the parameters.

Bastard Programmer from Hell :suss: