How to pass string parameter with `IN` operator in stored procedure SQL Server 2008

Question

I have a stored procedure when I execute it I got error

Conversion failed when converting the varchar value '+@dptId+' to data type int

I am getting DepartmentId as a string like (1,3,5,77) and am passing this to my stored procedure.

SQL FIDDLE

create table dummy (id int,name varchar(100),DateJoining Datetime, departmentIt int)

insert into dummy values (1,'John','2012-06-01 09:55:57.257',1);
insert into dummy  values(2,'Amit','2013-06-01 09:55:57.257',2);
insert into dummy  values(3,'Naval','2012-05-01 09:55:57.257',3);
insert into dummy  values(4,'Pamela','2012-06-01 09:55:57.257',4);
insert into dummy  values(5,'Andrea','2012-09-01 09:55:57.257',3);
insert into dummy  values(6,'Vicky','2012-04-01 09:55:57.257',4);
insert into dummy  values(7,'Billa','2012-02-01 09:55:57.257',4);
insert into dummy  values(8,'Reza','2012-04-01 09:55:57.257',3);
insert into dummy  values (9,'Jacob','2011-05-01 09:55:57.257',5);

Query I tried:

declare @startdate1 varchar(100) ='20120201'
declare @enddate1 varchar(100)='20130601'
declare @dptId varchar(100)='3,4'

select * 
from dummy
where DateJoining >= @startdate1 and DateJoining < @enddate1 
  and departmentIt IN (@dptId);

Freer

IN doesn't work like that. How about departmentIt = @dptId1 or departmentIt = @dptId2;? – Andersen

Earlier i was using simple Query string like departmentIt in (1,3) and its works, now when i use parameterized query am getting error. – Freer

departmentIt in (1,3) works but departmentIt in ('1,3') not, since it is only 1 value - a string and not 2 numbers. – Andersen

@juergend: yes i understand thats the error, but its also not posible to done using or coz am getting @dptId values as a string – Freer

Please do a little more searching as this is not an uncommon question. It can be done with dynamic SQL. Try Google with: sql dynamic "in" comma separated – Orlov

Answer 1

Here's how I solved it: Working SQL Fiddle

First I have create a function which splits the string value i.e. '1,2,4,5'

Split function:

CREATE  FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

Later in my query I use that split function

declare @startdate1 varchar(100) ='20120201'
declare @enddate1 varchar(100)='20130601'
declare @dptId varchar(100)='3,4'

select * from dummy
where DateJoining >=@startdate1 and DateJoining < @enddate1 
  and departmentID IN (SELECT Value FROM fn_Split(@dptId, ','));

Freer

@Santinder Singh is there open to SQL Injection? – Rarity

Answer 2

Try using sp_executesql as the answer. Not the most efficient but it works

ALTER PROCEDURE [dbo].[uspTestReportData_GetBySerial] 
    @SerialNumbers nvarchar(200)
AS
BEGIN
    SET NOCOUNT ON;
    declare @sql nvarchar(200)

    set @sql = 'SELECT * from MyTable WHERE  Serial_Number in (' + @SerialNumbers + ')'
    execute sp_executesql @sql                    

END

Streusel

Hi, this is not safe as someone can sql inject a dangerous clause. – Sprage

Answer 3

Simply, you can do the following SELECT:

SELECT M.REG_NO, T.TYPE_ID 
    FROM MAIN AS M 
        INNER JOIN CLASSIFICATION AS C 
            ON M.REG_NO = C.REG_NO
        INNER JOIN TYPE AS T 
            ON T.TYPE_ID = C.TYPE_ID
    WHERE (','+@Types+',') LIKE '%,' +T.TYPE_ID+ ',%'

Poree

This is the solution I would use. Simplicity is key. – Polyphony

Answer 4

ALTER PROCEDURE dbo.sp_Custom_Select_ClientVisit
(
    @ClientVisitId int = Null,
    @ClientId int = Null,
    @PersonId int = Null,
    @ProductId int = Null,

    @VisitDateFrom datetime = Null,
    @VisitDateTo datetime = Null,

    @eVisitStatusIn varchar(100) = Null,
    @eVisitStatus int = Null,
    @eStatus int = Null,
    @eStatusNot int = Null
)
AS

create table #IDs
(
    Id   int
)

Declare @delimiter varchar
Set @delimiter = ',' 

DECLARE @index int
SET @index = -1

WHILE (LEN(@eVisitStatusIn) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @eVisitStatusIn) 
    IF (@index = 0) AND (LEN(@eVisitStatusIn) > 0) 
      BEGIN  
        INSERT INTO #IDs VALUES (@eVisitStatusIn)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO #IDs VALUES (LEFT(@eVisitStatusIn, @index - 1))  
        SET @eVisitStatusIn = RIGHT(@eVisitStatusIn, (LEN(@eVisitStatusIn) - @index)) 
      END 
    ELSE
      SET @eVisitStatusIn = RIGHT(@eVisitStatusIn, (LEN(@eVisitStatusIn) - @index))
    END

Select 
    ClientVisit.ClientVisitId,      ClientVisit.eStatus,
    ClientVisit.VisitTime,          ClientVisit.VisitReason,
    ClientVisit.eVisitStatus,       ClientVisit.VisitSummary,

    Client.ClientId,        Client.InstituteName, 
    Client.PersonName as ClientPersonName,      Client.eStatus as ClienteStatus,

    Person.PersonId, Person.FirstName as ExecutiveFirstName, Person.LastName as ExecutiveLastName,
    Person.FirstName + ' ' + Person.LastName as ExecutiveName,

    p.ProductId, p.ParentProductId,
    p.ProductName, p.Description as ProductDescription,
    p.eStatus ProducteStatus,

    Case When ClientVisit.eVisitStatus = 1 Then 'Pending'
         When ClientVisit.eVisitStatus = 2 Then 'Completed'
         When ClientVisit.eVisitStatus = 3 Then 'Cancelled' End As VisitStatus,

    Case When ClientVisit.eStatus = 1 Then 'Active'
         When ClientVisit.eStatus = 2 Then 'Deactive'
         When ClientVisit.eStatus = 3 Then 'Deleted' End As Status


From AC_ClientVisit as ClientVisit
    INNER Join Com_Client Client On Client.ClientId = ClientVisit.ClientId
    INNER Join Com_Person Person On Person.PersonId = ClientVisit.ExecutiveId
    INNER Join Com_Product p On p.ProductId = Client.RootProductId

Where
    (@ClientVisitId         IS NULL OR ClientVisit.ClientVisitId        = @ClientVisitId)
AND (@ClientId              IS NULL OR Client.ClientId                  = @ClientId)
AND (@PersonId              IS NULL OR Person.PersonId                  = @PersonId)
AND (@ProductId             IS NULL OR p.ProductId                      = @ProductId)

AND (@VisitDateFrom         IS NULL OR @VisitDateFrom                   <= ClientVisit.VisitTime)
AND (@VisitDateTo           IS NULL OR @VisitDateTo                     >= ClientVisit.VisitTime)   

AND (@eVisitStatusIn        IS NULL OR ClientVisit.eVisitStatus IN(SELECT i.Id FROM #IDs AS i))
AND (@eVisitStatus          IS NULL OR ClientVisit.eVisitStatus             = @eVisitStatus)
AND (@eStatus               IS NULL OR ClientVisit.eStatus              = @eStatus)
AND (@eStatusNot            IS NULL OR ClientVisit.eStatus              <> @eStatusNot)
RETURN

Melly

You should add an explanation for how this will fix the issue. – Guidotti