一、拆分的存储过程
ALTER FUNCTION [dbo].[GLXSDeal_F](@guid VARCHAR(50),@str varchar(1000),@flag VARCHAR(10))
Returns @tableName Table
(
taskGuid VARCHAR(50),
str2table varchar(50)
)
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列,例如字符串'1,2,3,4,5' 将编程一个表,这个表
Begin
set @str = @str+@flag
Declare @insertStr varchar(50) --截取后的第一个字符串
Declare @newstr varchar(1000) --截取第一个字符串后剩余的字符串
set @insertStr = left(@str,charindex(@flag,@str)-1)
set @newstr = stuff(@str,1,charindex(@flag,@str),'')
Insert @tableName Values(@guid,@insertStr)
while(len(@newstr)>0)
begin
set @insertStr = left(@newstr,charindex(@flag,@newstr)-1)
Insert @tableName Values(@guid,@insertStr)
set @newstr = stuff(@newstr,1,charindex(@flag,@newstr),'')
end
Return
End
二、调用拆分过程的存储过程,可以传递参数,按传递的参数拆分字符串
ALTER PROCEDURE [dbo].[StrToTable]
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列,例如字符串'1,2,3,4,5' 将编程一个表,这个表
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = object_id(N'[#TmpGLXS]') AND OBJECTPROPERTY(ID, 'IsTable') = 1)
drop table #TmpGLXS
CREATE Table #TmpGLXS
(
taskGuid VARCHAR(32),
str2table varchar(50)
)
Begin
Declare @CTLGuid varchar(1000)
Declare @str varchar(20)
Declare Cur Cursor For
SELECT CTLGuid,IGLXS FROM dbo.TaskLineBudget
Open Cur
Fetch next From Cur Into @CTLGuid,@str
While @@fetch_status=0
BEGIN
INSERT INTO #TmpGLXS(taskGuid,str2table)
--flag 为 ','
SELECT * FROM [dbo].[GLXSDeal_F](@CTLGuid,@str,',')
Fetch next From Cur Into @CTLGuid,@str
End
Close Cur
Deallocate Cur
SELECT * FROM #TmpGLXS
End