Conversion failed when converting the varchar value to data type int.

Table:Student
Name nvarchar(1000)
Status int

In the stored procedure - I have got a string which contains comma separated Status Values such as

DECLARE @validStatus = '1000,1001,1002'

I want to return the count of students having the status code as in @validStatus

SELECT count(1) FROM Student WHERE Status in (@validStatus)

But the above statement is erroring out with

Conversion failed when converting the varchar value '1000,1001,1002' to data type int.

Any help/suggestion appreciated.

Thanks,
Loonysan

[683 byte] By [loonysan] at [2007-12-24]

«« monitoring w3wp.exe in a load test
»» VS 2005 errors when creating new web - permissions

# 1

The In syntax does not work that way with variables. The variable is treated as single value and not as a list of values. There are two ways to work with a list of values. You can use Dynamic SQL and concatenate the list into the IN clause. Or you can convert the list of values into a table -- temp table or better still a table variable -- and use a Join instead of the IN syntax. You will probably want to write a user defined function for this.

anomolous at 2007-10-8 >

top of Msdn Tech,SQL Server,Transact-SQL...

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech