例如以下的sql语句
select * from Tags where Name in('ruby','rails','scruffy','rubyonrails')
写成这样, 编译不通过
declare @nameList nvarchar(100) select @nameList = 'aaa,bbb,ccc,ddd'
select * from Tags where Name in @nameList
如果还是想简单的传入一个字符串代表 in 的目标 , 可以:
一 : 先建一个函数 , split 传入的参数 , 存到临时表里
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fnSplit]( @sInputList VARCHAR(8000) -- List of delimited items , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items ) RETURNS @List TABLE (item VARCHAR(8000)) BEGIN DECLARE @sItem VARCHAR(8000) WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0 BEGIN SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) IF LEN(@sItem) > 0 INSERT INTO @List SELECT @sItem END IF LEN(@sInputList) > 0 INSERT INTO @List SELECT @sInputList -- Put the last item in RETURN END
二 : 然后把改写 sql语句
DECLARE @tags
SET @tags = ‘ruby,rails,scruffy,rubyonrails’ select * from Tags where Name in (SELECT item from fnSplit(@tags, ‘,’))
三: 这样,所有的拆分工作都放在数据库完成, 外部程序只需传入一个简单的字符串就可以。
此方法来自 stackoverflow, 原帖地址见
http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause?rq=1