如何在存储过程 SQL Server 2008 中使用`IN` 运算符传递字符串参数 [英] How to pass string parameter with `IN` operator in stored procedure SQL Server 2008
本文介绍了如何在存储过程 SQL Server 2008 中使用`IN` 运算符传递字符串参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个存储过程,当我执行它时出现错误
I have a stored procedure when I execute it I got error
将 varchar 值 '+@dptId+' 转换为数据类型 int 时转换失败
Conversion failed when converting the varchar value '+@dptId+' to data type int
我将 DepartmentId
作为类似 (1,3,5,77)
的字符串获取,并将其传递给我的存储过程.
I am getting DepartmentId
as a string like (1,3,5,77)
and am passing this to my stored procedure.
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);
推荐答案
我是这样解决的:工作 SQL 小提琴
Here's how I solved it: Working SQL Fiddle
首先,我创建了一个函数,它拆分字符串值,即 '1,2,4,5'
First I have create a function which splits the string value i.e. '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
稍后在我的查询中我使用了拆分函数
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, ','));
这篇关于如何在存储过程 SQL Server 2008 中使用`IN` 运算符传递字符串参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文