DP44/DataPRO_sql/dbo.sp_DBImportGroupTemplate.StoredProcedure.sql

<EFBFBD><EFBFBD>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportGroupTemplate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DBImportGroupTemplate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportGroupTemplate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportGroupTemplate] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_DBImportGroupTemplate]
		 @GroupTemplates			xml
		,@errorNumber				int				output
		,@errorMessage				nvarchar(250)	output 
AS
BEGIN
	set @errorNumber = 0 
	set @errorMessage = space(0)

	declare @tGroupTemplates table(
		  TemplateName				nvarchar(255) 
		, Icon						nvarchar(50) 
		, [Description]				nvarchar(50) 
		, LocalOnly					bit 
		, [Version]					int 
		, LastModified				datetime 
		, LastModifiedBy			nvarchar(50) 
		, CRC32						int 
		, ISOTestObject				nvarchar(255) 
		, TestObjectType			nvarchar(255) 
		, ParentTemplate			nvarchar(50) 
		, SysBuilt					bit 
		, OrigTemplateName			nvarchar(255) 
		, Embedded					bit
		, TemplateChannels			xml
		, Processed					bit)

	insert into @tGroupTemplates
	select     t.x.value('(TemplateName)[1]'		, 'nvarchar(255)') 
 			 , t.x.value('(Icon )[1]'				, 'nvarchar(50)')
 			 , t.x.value('(Description )[1]'		, 'nvarchar(50)')
 			 , t.x.value('(LocalOnly)[1]'			, 'bit')
 			 , t.x.value('(Version)[1]'				, 'int')
 			 , t.x.value('(LastModified)[1]'		, 'datetime')
 			 , t.x.value('(LastModifiedBy)[1]'		, 'nvarchar(50)')
 			 , t.x.value('(CRC32)[1]'				, 'int')
 			 , t.x.value('(TestObjectType)[1]'		, 'nvarchar(255)')
 			 , t.x.value('(TestObject)[1]'			, 'nvarchar(255)')
 			 , t.x.value('(ParentTemplate)[1]'		, 'nvarchar(50)')
 			 , t.x.value('(SysBuilt)[1]'			, 'bit')
 			 , case when ltrim(rtrim(isnull(t.x.value('(OriginalTemplateName)[1]', 'nvarchar(255)'), space(0)))) = space(0) 
				then '[NONE]' 
				else t.x.value('(OriginalTemplateName)[1]', 'nvarchar(255)') end as OriginalTemplateName
 			 , t.x.value('(Embedded)[1]'			, 'bit')
			 , t.x.query('(TemplateChannels)')
			 , 0
	from @GroupTemplates.nodes('/GroupTemplates/GroupTemplate') t(x)

	while (Select Count(*) From @tGroupTemplates Where Processed = 0) > 0
		Begin
			begin try
				begin transaction tTestObject
				declare	  @TemplateId				int 
						, @TemplateName				nvarchar(255) 
						, @Icon						nvarchar(50) 
						, @Description				nvarchar(50) 
						, @LocalOnly				bit 
						, @Version					int 
						, @LastModified				datetime 
						, @LastModifiedBy			nvarchar(50) 
						, @CRC32					int 
						, @ISOTestObject			nvarchar(255) 
						, @TestObjectType			nvarchar(255) 
						, @ParentTemplate			nvarchar(50) 
						, @SysBuilt					bit 
						, @OrigTemplateName			nvarchar(255) 
						, @Embedded					bit
						, @TemplateChannels			xml

				select top 1
						  @TemplateName		= TemplateName	
						, @Icon				= Icon			
						, @Description		= [Description]	
						, @LocalOnly		= LocalOnly		
						, @Version			= [Version]		
						, @LastModified		= LastModified	
						, @LastModifiedBy	= LastModifiedBy	
						, @CRC32			= CRC32			
						, @ISOTestObject	= ISOTestObject	
						, @TestObjectType	= TestObjectType	
						, @ParentTemplate	= ParentTemplate	
						, @SysBuilt			= SysBuilt		
						, @OrigTemplateName	= OrigTemplateName
						, @Embedded			= Embedded		
						, @TemplateChannels	= TemplateChannels
				from @tGroupTemplates	where Processed = 0
				
				/* add Template */
				exec [dbo].[sp_TestObjectTemplatesUpdateInsert] @TemplateName		
																,@Icon				
																,@Description		
																,@LocalOnly			
																,@Version			
																,@LastModifiedBy		
																,@CRC32				
																,@ISOTestObject		
																,@TestObjectType		
																,@LastModified		
																,@ParentTemplate		
																,@SysBuilt			
																,@OrigTemplateName	
																,@Embedded			
																,@TemplateId				output
																,@errorNumber				output
																,@errorMessage				output 

					if(@TemplateId != 0)
					begin
						/* add Template Channels */
						exec [dbo].[sp_DBImportGroupTemplateChannels] @TemplateId, @TemplateChannels, @errorNumber output, @errorMessage output 
					end

					update @tGroupTemplates set [Processed] = 1 where TemplateName = @TemplateName

				commit transaction tTestObject
			end try
			begin catch
				set @errorNumber	= error_number()
				set @errorMessage	= error_message()
				rollback transaction tTestObject
			end catch
		end
END
	



























GO