SQL IN Statement Only Accepts Integer

[Shamas21]

Hi All

I have the following Stored Procedure that asks for parameter @CaseID in my table that is type INT but how can I insert the values in the parameter when I MUST use string as the variable as the IN statement it only accepts a "," (comma) to sperate the values.

for example

CREATEPROCEDURE [dbo].[spEvents]

	@CaseID nvarchar(10)

AS

SELECT     tblEvents.CaseID
FROM       tblEvents
WHERE     (tblEvents.CaseID IN (@CaseID))

But using the IN statement it accepts multiple values but it needs to be seperated by a comma "," therefor this becomes a string value. The below is the proper syntax for the IN statement

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

so when I goto execute my stored procedure like

exec spEvents @CaseID = '21474,21475'

It comes back with the following error

Msg 245, Level 16, State 1, Procedure spEvents, Line 7
Conversion failed when converting the nvarchar value '21474,2147' to data type int.

Any ideas on how I can overcome this problem?

So if I use the statement as follows it works fine without the parameter

SELECT     tblEvents.CaseID
FROM       tblEvents
WHERE     (tblEvents.CaseID IN (21474,21475))

[si_the_geek]

But using the IN statement it accepts multiple values but it needs to be seperated by a comma "," therefor this becomes a string value.

No it does not, it stays as multiple values separated by commas.

(value1,value2,...) is not the same thing as ('value1,value2,...')


In order to use a single parameter for a list of values you will need to split them out somehow (such as shown in the CodeBank threads Using Parameters with an SQL IN Clause and SQL Server 2005 - Loop through/split a delimited string), or do away with the benefits of a stored procedure and within the SP build+execute a string containing the query and values.

[Shamas21]

No it does not, it stays as multiple values separated by commas.

(value1,value2,...) is not the same thing as ('value1,value2,...')


In order to use a single parameter for a list of values you will need to split them out somehow (such as shown in the CodeBank threads Using Parameters with an SQL IN Clause and SQL Server 2005 - Loop through/split a delimited string), or do away with the benefits of a stored procedure and within the SP build+execute a string containing the query and values.

Many Thanks. I decided to build the SQL statement in my VB project instead by using the

            cmd.CommandType = CommandType.Text
            cmd.CommandText = finalSQL

[brucevde]

Another option would have been to use the CharIndex function.

WHERE CharIndex( Cast(CaseId as varchar) + ',' , @CaseId) > 0

The + ',' is required to ensure an exact match. Otherwise searching for Case Ids 1,77, would return Id 7 as well. Also note the last character in @CaseId must be a comma.

[Shamas21]

Another option would have been to use the CharIndex function.

WHERE CharIndex( Cast(CaseId as varchar) + ',' , @CaseId) > 0

The + ',' is required to ensure an exact match. Otherwise searching for Case Ids 1,77, would return Id 7 as well. Also note the last character in @CaseId must be a comma.

Hi Bruce

I need your help. Can you alter the following statement for me to accept mutiple values by using you CharIndex method? Any help is appreciated. Thanks

ALTER PROCEDURE [dbo].[spReportsInvoicedByConsultant]

	@FromDate datetime,
	@ToDate datetime,
	@ConsultantName nvarchar(MAX)

AS

SELECT     tblCases.Consultant, SUM(tblInvoicedCharges.Net) AS SumOfNet
FROM         tblCases INNER JOIN
                      tblInvoicedCharges ON tblCases.CaseID = tblInvoicedCharges.CaseID
WHERE     (tblInvoicedCharges.Date >= @FromDate) AND (tblInvoicedCharges.Date <= @ToDate) AND (tblCases.Consultant IN (@ConsultantName))
GROUP BY tblCases.Consultant
ORDER BY SumOfNet DESC