SQL IN Statement Only Accepts Integer

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

Code:

---

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

Code:

---

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

---

so when I goto execute my stored procedure like

Code:

---

exec spEvents @CaseID = '21474,21475'

---

It comes back with the following error

Code:

---

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

Code:

---

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

---

Quote:

---

Originally Posted by Shamas21

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.

Quote:

---

Originally Posted by si_the_geek

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

Code:

---

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

---

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.

Quote:

---

Originally Posted by 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.

---

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

Code:

---

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

---