Split Stored Procedure
Create proc spSplitValues(@Str varchar(8000)) as
begin
Declare @Table Table(RowNum int identity,SplitedValue varchar(300))
declare @String nvarchar(4000)
set @String = '' + replace(@Str,',',' ') + '
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @String
Insert into @Table SELECT * FROM OPENXML(@hDoc, '/Root/Sub',2)
with (SplitValue varchar(300))
select * from @Table where len(SplitedValue)>0
EXEC sp_xml_removedocument @hDoc
end
Exec spSplitValues 'a,b,c,d,e,f'
begin
Declare @Table Table(RowNum int identity,SplitedValue varchar(300))
declare @String nvarchar(4000)
set @String = '
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @String
Insert into @Table SELECT * FROM OPENXML(@hDoc, '/Root/Sub',2)
with (SplitValue varchar(300))
select * from @Table where len(SplitedValue)>0
EXEC sp_xml_removedocument @hDoc
end
Exec spSplitValues 'a,b,c,d,e,f'
posted by Madhu K Nair at
9:57 PM
2 Comments:
WHat about using a function:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1
-Jens
Yes Jens, my first and last choice is always your function and at least i have refered the function not less than 30 times. Whoever read this post, this is just a useless alternative.
Madhu :)
Post a Comment
Subscribe to Post Comments [Atom]
<< Home