解决SQL中in参数在存储过程中的传递问题
解决SQL中in参数在存储过程中的传递问题
CSDN中有这样一个帖子
地址：http://bbs.csdn.net/topics/110108650
问题：
比如有一个SQL
SELECT * FROM SELTEXT WHERE ID IN (1,2,3,4)
我希望创建一个存储过程
比如：
CREATE PROCEDURE [INSelect]
@P_0 NVARCHAR(600)
AS
SELECT * FROM SELTEXT WHERE ID IN (@P_0)
创建此存储过程没有问题，执行时总是有错误。
如上问题就是SQL中in参数在存储过程中的传递问题，我们在实际做项目中也遇到类似问题。
解决这类问题有三个途径
一，用程序凑成完整的SQl再程序中执行。毕竟程序比存储过程容易调试。
二，把存储过程中sql语句，变成字符串，拼凑SQL用Exec执行。这种改造方式工作量很大。
例如：改成
Set @sql=''
exec @sql
这种模式
三，综合上述两种方式，程序的修改量，工作量很大。于是我就探索了另外一种模式。详细介绍给大家。
1.建立一个函数，将传递的字符参数分解成临时表中的列。
2.通过表关联实现。
举例：
1)SELECT [b404id]
,[b404no]
,[b404name]
,[b404parentno]
,[b404status]
FROM [GwInterestDB].[gwamis].[c404jklb]
where b404no in ('010000','011000','012000','020000','020100')
--查询结果--
01 010000 经费借款 0 1
02 011000 2012年以前经费 010000 1
03 012000 2012年以后经费 010000 1
04 020000 投资类借款 0 1
05 020100 股权 020000 1
这个就是最终的效果，不管是存储过程也好，程序实现也好，都要形成以上查询语句。
关键问题就出现在这里。如果把“'010000','011000','012000','020000','020100'”直接作为参数传递就回出现问题。
解决方法如下：
2）建立函数f_split
--------------------------------------
--作者:zhaoyu
--时间:20120726
--功能:分割字符串
--------------------------------------
create function [dbo].[f_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
GO
--执行函数返回表
select * from [dbo].[f_split]('010000,011000,012000,020000,020100',',')
--运行结果如下
col
010000
011000
012000
020000
020100
这里面关键点关注字符串，由原来的“'010000','011000','012000','020000','020100'”变成了
'010000,011000,012000,020000,020100'。再传递就不会产生单引号的麻烦。
3）最终sql变成了
SELECT [b404id]
,[b404no]
,[b404name]
,[b404parentno]
,[b404status]
FROM [GwInterestDB].[gwamis].[c404jklb]
where b404no in (select col from [dbo].[f_split]('010000,011000,012000,020000,020100',','))
--查询结果--
01 010000 经费借款 0 1
02 011000 2012年以前经费 010000 1
03 012000 2012年以后经费 010000 1
04 020000 投资类借款 0 1
05 020100 股权 020000 1
结果与预测的一样满足要求。
总结：
这样通过引入一个函数，in中需要传递参数的地方改为了表关联，而不是直接应用就避开了单引号问题。
此法可以作为一个通用的方法，解决此类问题。
或者用一下函数:
create Function [dbo].[GetSplitedValues](@Ids nvarchar(max))
returns @Table Table(Id nvarchar(4000))
As
Begin
Declare @ind int
Declare @Start int
Set @Ids=@Ids+','
Set @Start=1
Set @ind =charindex(',',@Ids)
While(@ind>0)
Begin
Declare @SubId nvarchar(100)
Set @SubId=Substring(@Ids,@Start,(@ind-1))
if(@SubId<>'' and @SubId Is Not Null)
Begin
insert into @Table(Id) values(@SubId)
End
Set @Ids =Substring(@Ids,@ind+1,Len(@Ids))
SET @ind = charindex(',',@Ids )
End
Return
END
调用:
SELECT * FROM tab where city in (select * from GetSplitedValues( @city))
