stored-procedures - 如何在存储过程 SQL Server 2008 中使用 `IN` 运算符传递字符串参数

Question

我有一个存储过程，当我执行它时出现错误

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

我收到 DepartmentId作为一个字符串，如 (1,3,5,77)并将其传递给我的存储过程。

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);

我试过的查询:

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);

Answer 1

我是这样解决的: Working SQL Fiddle

首先我创建了一个函数 拆分 字符串值，即 '1,2,4,5'
拆分功能 :

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

稍后在我的查询中，我使用该拆分功能

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, ','));