If I have a string of Integers passed into a Stored Proc as a Varchar
string, is there a way to use this string in a "Where [column_name] in
(@variable)" without using an EXEC sp_executesql statement? The problem
I run into is the column is a smallint and I get the error:

Msg 245, Level 16, State 1, Line 11
Syntax error converting the nvarchar value '0,-1,5' to a column of data
type smallint.

Here is the test case the duplicates my error:


CREATE TABLE Tester (number smallint)

INSERT INTO tester
values(-1)
INSERT INTO tester
values(0)
INSERT INTO tester
values(2)
INSERT INTO tester
values(3)
INSERT INTO tester
values(4)
INSERT INTO tester
values(5)

DECLARE @var varchar(25)

SET @var = '0,-1,5'

SELECT * FROM tester WHERE number IN (@var)

--drop table Tester

I know I can break the incoming string into individual variables, but it
seems there should be a better way to accomplish this.

Any help or advice is much appreciated!! As will a good pint of Ale!!

Stephen
-----------------------------------------
The information contained in this e-mail message is intended only
for the personal and confidential use of the recipient(s) named
above. This message may be an attorney-client communication and/or
work product and as such is privileged and confidential. If the
reader of this message is not the intended recipient or an agent
responsible for delivering it to the intended recipient, you are
hereby notified that you have received this document in error and
that any review, dissemination, distribution, or copying of this
message is strictly prohibited. If you have received this
communication in error, please notify us immediately by e-mail, and
delete the original message.

MS Sql Server LazyDBA home page