存储过程中传数组字符串

     
   
  create procedure C_DELETE_COMMENT
  (@CommentID varchar(100))
  AS
  EXEC ('delete FROM sysComment WHERE CommentID IN (' + @CommentID + ')')
  
  GO
  需要使用Substring(0,temcommentid.Length-1);消除字符串最后的一个逗号
  
  
  
  字符串处理
  
  
  1.存储过程实现
  
  create procedure sp_split_string
  (
   @string NVARCHAR(4000)
  )
  AS
  BEGIN
   DECLARE @object_id nvarchar(500)
   DECLARE @i INT
   DECLARE @len INT
   print @string
   IF (@string IS NULL) OR (LTRIM(@string) = '')
   RETURN
  
   WHILE CHARINDEX(',',@string) > 0
   BEGIN
   SET @len = LEN(@string)
   SET @i = CHARINDEX(',', @string)
   SET @object_id = LEFT(@string, @i-1)
  
   INSERT INTO a (id) VALUES (@object_id)--少做修改，改成需要的sql语句即可
   SET @string = RIGHT(@string, @len - @i)
   END
   SET @object_id = @string
   INSERT INTO a (id) VALUES (@object_id)--少做修改，改成需要的sql语句即可
  END
  go
  
  --测试
  -- select * from a
  -- exec sp_split_string '102,103,105,106,107,108,200,500,306,408'
  -- select * from a
  2.一个用于处理分隔","的函数tf_split_str
  
  
  --1. 建立测试数据
  --1.1 create table TypeAdv
  CREATE TABLE TypeAdv
  (
   id int,
   TypeName varchar(200)
  )
  --1.2 insert data
  insert into TypeAdv(id,TypeName) values(1,'电脑网络')
  insert into TypeAdv(id,TypeName) values(2,'游戏动漫')
  insert into TypeAdv(id,TypeName) values(3,'软件下载')
  insert into TypeAdv(id,TypeName) values(4,'商务商铺')
  insert into TypeAdv(id,TypeName) values(5,'建站服务')
  insert into TypeAdv(id,TypeName) values(6,'门户综合')
  insert into TypeAdv(id,TypeName) values(7,'影视音乐')
  insert into TypeAdv(id,TypeName) values(8,'休闲娱乐')
  insert into TypeAdv(id,TypeName) values(9,'生活资讯')
  insert into TypeAdv(id,TypeName) values(10,'文学小说')
  
  --1.3 create table TypeAdv
  CREATE TABLE GroupAdv
  (
   id int,
   GroupName varchar(200),
   IntentionSet varchar(200)
  )
  --1.4 insert data
  insert into GroupAdv(id,GroupName,IntentionSet) values(5,'广告分组一','1,2,3,4,5,6,7,10')
  insert into GroupAdv(id,GroupName,IntentionSet) values(6,'广告分组二','1,2,3,4,5,6')
  --2. 创建分隔“，”的函数
  CREATE FUNCTION tf_split_str
  (
   @string NVARCHAR(4000)
  )
  RETURNS varchar(4000)
  AS
  BEGIN
   DECLARE @object_id nvarchar(400)
   DECLARE @i INT
   DECLARE @len INT
   declare @return varchar(400)
   set @return=''
  
   IF (@string IS NULL) OR (LTRIM(@string) = '')
   RETURN @return
  
   WHILE CHARINDEX(',',@string) > 0
   BEGIN
   SET @len = LEN(@string)
   SET @i = CHARINDEX(',', @string)
   SET @object_id = LEFT(@string, @i-1)
   select @return = @return+','+TypeName from TypeAdv where id = @object_id
   SET @string = RIGHT(@string, @len - @i)
  
   END
  
   SET @object_id = @string
   select @return = @return+','+TypeName from TypeAdv where id = @object_id
   set @return = stuff(@return,1,1,'')
   RETURN @return
  END
  --3.测试
  select b.id,b.GroupName,dbo.tf_split_str(b.IntentionSet) from GroupAdv b  

（编辑：IT资讯之家 www.it55.com）