Hey Stephen

How about converting the value of column name to a varchar/nvarchar?

Then instead of "Where [column_name] in (@variable)"
use "Where charindex(',' + convert(varchar,[column_name]) + ',',
',' + replace(@variable, ' ', '') + ',') > 0"

Note - I added commas to either side of the value so 1 is not found when 10 is in the list - so had to add a comma to both ends of the string too. I have also dumped spaces in the value list

Sandie

-----Original Message-----
From: Stephen Dyckes
[mailto:mssqldba-ezmlmshield-x38326156.[Email address protected]
Sent: Tuesday, March 27, 2007 9:39 AM
To: LazyDBA Discussion
Subject: Passing a variable within an in statement


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.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page