把字符串传到存储过程中分割

有时候需要把多个值同时传到存储过程中去
为了避免造成程序与数据库多次交互

可以把多个ID用某个符号分隔，如"1,2,3"，用一个字符串传到存储过程里

注意：把最后的那个符号去掉，应是" 1,2,3 "而不是" 1,2,3, "


创建分解函数：
/*--------------------------------------------------------------------------------------------
[描述]
 分解字符串，然后将字符串存放在Table中
--------------------------------------------------------------------------------------------*/
create FUNCTION [dbo].[uf_Split]
(
 @Text NVARCHAR(4000),
 @Split NVARCHAR(50) = ','
)
RETURNS @Table TABLE([Id] INT IDENTITY(1, 1) NOT NULL, [Value] NVARCHAR(4000))
AS
BEGIN
 DECLARE @Oldword        NVARCHAR(4000)
 DECLARE @FirstWord  NVARCHAR(4000)
    DECLARE @Length         INT
    DECLARE @CommaLocation  INT
    SELECT @Oldword = @Text

 SELECT @CommaLocation = CHARINDEX(@Split, @Oldword)
    WHILE (@CommaLocation > 0) AND (@Oldword IS NOT NULL)
    BEGIN
        SELECT @CommaLocation = CHARINDEX(@Split, @Oldword)
        SELECT @Length = DATALENGTH(@Oldword)
        SELECT @FirstWord = SUBSTRING(@Oldword, 1, @CommaLocation -1)
        SELECT @Oldword = SUBSTRING(@Oldword, @CommaLocation + 1, @Length - @CommaLocation)
       
        INSERT INTO @Table([Value]) VALUES(@FirstWord)
  SELECT @CommaLocation = CHARINDEX(@Split, @Oldword)
    END
 IF @Oldword IS NOT NULL
  INSERT INTO @Table([Value]) VALUES(@Oldword)
 
 RETURN
END


调用：
方法一

select * from uf_Split('1,2,3,4',',')

方法二
declare @value nvarchar(50)
set @value = 'a,b,c'
select * from uf_Split(@value,',')

然后可以遍历这个TABLE

 

 

 

-----------------------------------------------------------------------------------------------------------------------------

 

 

1.利用replace

create table #temp
(
ss varchar(200) not null
)
declare @str varchar(200)
declare @result varchar(1000)
set @str='aaa,bb,c,d,e,ffffff'
set @result =' insert into #temp(ss) select '''+replace(@str,',','''union select''')+''''
exec(@result)
select * from #temp
2.利用charindex和substring

create table #temp
(
ss varchar(200) not null
)
declare @str varchar(200)
declare @curr int
declare @prev int
set @str='aaa,bb,c,d,e,ffffff'
set @curr=1
set @prev=1
while  @prev < len(@str)
begin
set @curr=charindex(',',@str,@prev)
if @curr>@prev
insert #temp select substring(@str,@prev,@curr-@prev)  
else
begin
insert  #temp select substring(@str,@prev,len(@str)-@prev+1)
break
end
set @prev=@curr+1
end
select * from #temp