Cacher is the code snippet organizer for pro developers

USE [TestDB]
GO


CREATE PROCEDURE [dbo].[test_sp]
@data1 nvarchar(max),
@data2 nvarchar(max),
@data3 nvarchar(max)
AS
BEGIN
	set nocount on;

	declare @xml1 XML, @xml2 XML, @xml3 XML
	declare @delimiter nvarchar(2)

	set @delimiter = ','

	select @xml1 = cast('<d>' + replace(@data1, @delimiter, '</d><d>') + '</d>' as XML)
	select @xml2 = cast('<d>' + replace(@data2, @delimiter, '</d><d>') + '</d>' as XML)
	select @xml3 = cast('<d>' + replace(@data3, @delimiter, '</d><d>') + '</d>' as XML)

	declare @tb1 table (id int identity(1,1), name nvarchar(12))
	declare @tb2 table (id int identity(1,1), value int)
	declare @tb3 table (id int identity(1,1), value int)

	insert into @tb1 select T1.split.value('.', 'nvarchar(max)') as data1 from @xml1.nodes('/d') T1(split)	
	insert into @tb2 select T2.split.value('.', 'int') as data2 from @xml2.nodes('/d') T2(split)	
	insert into @tb3 select T3.split.value('.', 'int') as data3 from @xml3.nodes('/d') T3(split)

	declare @param_tb table (name nvarchar(32), val1 int, val2 int)
	insert into @param_tb select t1.name name, t2.value val1, t3.value val2
			from @tb1 t1, @tb2 t2, @tb3 t3
			where t1.id = t2.id and t1.id = t3.id;

	declare my_cur cursor fast_forward for select * from @param_tb

	declare @name nvarchar(32)
	declare @val1 int, @val2 int

	begin try
		begin tran

		open my_cur

		fetch next from my_cur into @name, @val1, @val2
		while @@FETCH_STATUS = 0
		begin
			declare @ret int

			exec @ret =  test_sp2 @name, @val1, @val2
			if @ret != 0
			begin
				close my_cur
				deallocate my_cur
				return -1
			end

			fetch next from my_cur into @name, @val1, @val2
		end

		close my_cur
		deallocate my_cur

		if @@TRANCOUNT > 0
			commit tran
	end try
	begin catch
		if @@TRANCOUNT > 0
			rollback tran
	end catch

	
END

GO