Passing in a where string for "in" statement

[jimmythegeek]

The following returns this error (Syntax error converting the varchar value '10, 12, 15' to a column of data type int). The variables are actually input parameters in a function, and the '10, 12, 15' string is passed in:

----------
declare @appID int
declare @qID varchar(100)

set @appID = 113
set @qID = '10, 12, 15'

select a.AnswerContent
from ogms.dbo.tblAppAnswer a inner join ogms.dbo.tblQuestionPart p ON a.QPartID = p.QPartID
where a.appID = @appID and p.qID in (@qID)

----------

If this method won't work, how can I pass in a string of values to use as part of an "in" where statement?

Thanks in advance for any input.



Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[SQLBill]

First, SQL Server is seeing that as a single string not as three separate numbers.

For example:

'1, 234' is seen as 1,234 not as two numbers, 1 and 234.

You need to have them inputed as '10','12','15'

Then CONVERT the p.qID into VARCHAR datatype.

This should work:

CAST(p.qID AS VARCHAR(10)) IN @q.ID

You will need to make the VARCHAR long enough to hold your largest value.

-SQLBill


Posting advice: FAQ481-4875