IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportTestEngineerDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DBImportTestEngineerDetails]
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_DBImportTestEngineerDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportTestEngineerDetails] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_DBImportTestEngineerDetails]
		 @TestEngineerDetails		xml
		,@errorNumber				int				output
		,@errorMessage				nvarchar(250)	output
AS
BEGIN
		set @errorNumber  = 0
		set @errorMessage = ''

		declare @tTestEngineerDetails table (
				[Name]				[nvarchar](255) NULL,
				[TestEngineerName]	[nvarchar](255) NULL,
				[TestEngineerPhone] [nvarchar](255) NULL,
				[TestEngineerFax]	[nvarchar](255) NULL,
				[TestEngineerEmail] [nvarchar](255) NULL,
				[LocalOnly]			[bit]			NULL,
				[LastModified]		[datetime]		NULL,
				[LastModifiedBy]	[nvarchar](50)	NULL,
				[Version]			[int]			NULL)
		begin try
			begin transaction tTestEngineerDetails
			insert into @tTestEngineerDetails
				([Name]				
				,[TestEngineerName]	
				,[TestEngineerPhone] 
				,[TestEngineerFax]	
				,[TestEngineerEmail] 
				,[LocalOnly]			
				,[LastModified]		
				,[LastModifiedBy]	
				,[Version])		
				
			select 
				   t.x.value('@Name', 'varchar(255)')							
				  ,t.x.value('@TestEngineerName', 'varchar(255)')							
				  ,t.x.value('@TestEngineerPhone', 'varchar(255)')							
				  ,t.x.value('@TestEngineerFax', 'varchar(255)')							
				  ,t.x.value('@TestEngineerEmail', 'varchar(255)')							
				  ,t.x.value('@LocalOnly', 'bit')							
				  ,t.x.value('@LastModified', 'datetime')							
				  ,t.x.value('@LastModifiedBy', 'varchar(50)')			
				  ,t.x.value('@Version', 'int')	
			from @TestEngineerDetails.nodes('/TestEngineerDetails/TestEngineerDetail') t(x)
			OPTION (OPTIMIZE FOR ( @TestEngineerDetails = NULL ))

			insert into [dbo].[TestEngineerDetails] select * from @tTestEngineerDetails t where t.[Name] is not null
			
			commit transaction tTestEngineerDetails
		end try
		begin catch
			set @errorNumber  = error_number()
			set @errorMessage = error_message()
			rollback transaction tTestEngineerDetails
		end catch;
END



























GO