存储过程中传入多个ID以及分割问题

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

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


可以把多个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

转载于:https://www.cnblogs.com/lishenglyx/archive/2008/12/16/1356135.html