tsql字符串分割问题

问题如下：

某一个存储过程，过程体是：select * from atable where afield in(@str)

在外面调用的格式是:exec dbo.proc1 'a,b,c'

实现代码如下：

 

代码

if object_id ( ' dbo.fn_split ' ) is not null drop function dbo.fn_split
go
create function dbo.fn_split
(
@inputstr varchar ( 8000 ),
@seprator varchar ( 10 )
)
returns @temp table (a varchar ( 200 ))
as
begin
declare @i int
set @inputstr = rtrim ( ltrim ( @inputstr ))
set @i = charindex ( @seprator , @inputstr )
while @i >= 1
begin
insert @temp values ( left ( @inputstr , @i - 1 ))
set @inputstr = substring ( @inputstr , @i + 1 , len ( @inputstr ) - @i )
set @i = charindex ( @seprator , @inputstr )
end
if @inputstr <> ' \ '
insert @temp values ( @inputstr )
return
end
go
alter PROCEDURE dbo.Myproc1
@str nvarchar ( 100 )
as
begin
select id,name from dbo.customer where name in ( select * from dbo.fn_split( @str , ' , ' ))
end

exec dbo.Myproc1 ' 吉之岛,北京家乐福 '

 

 

转载于:https://www.cnblogs.com/cnbwang/archive/2010/12/18/1909787.html