Thursday, November 22, 2007

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'

2 Comments:

Blogger Jens K. Süßmeyer said...

WHat about using a function:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

-Jens

1:24 AM  
Blogger Madhu K Nair said...

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 :)

1:36 AM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home