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'