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

		declare @tCustomerDetails table (
			[Name]					[nvarchar](255) NULL,
			[CustomerName]			[nvarchar](255) NULL,
			[CustomerTestRefNumber] [nvarchar](255) NULL,
			[ProjectRefNumber]		[nvarchar](255) NULL,
			[CustomerOrderNumber]	[nvarchar](255) NULL,
			[CustomerCostUnit]		[nvarchar](255) NULL,
			[LocalOnly]				[bit]			NULL,
			[LastModified]			[datetime]		NULL,
			[LastModifiedBy]		[nvarchar](50)	NULL,
			[Version]				[int]			NULL)	

		begin try
			begin transaction tCustomerDetails

			insert into @tCustomerDetails
				([Name] 
				,[CustomerName] 
				,[CustomerTestRefNumber] 
				,[ProjectRefNumber] 
				,[CustomerOrderNumber] 
				,[CustomerCostUnit] 
				,[LocalOnly] 
				,[LastModified] 
				,[LastModifiedBy]
				,[Version])	
			select 
				   t.x.value('@Name', 'varchar(255)')							
				  ,t.x.value('@CustomerName', 'varchar(255)')							
				  ,t.x.value('@CustomerTestRefNumber', 'varchar(255)')							
				  ,t.x.value('@ProjectRefNumber', 'varchar(255)')							
				  ,t.x.value('@CustomerOrderNumber', 'varchar(255)')							
				  ,t.x.value('@CustomerCostUnit', 'varchar(255)')							
				  ,t.x.value('@LocalOnly', 'bit')							
				  ,t.x.value('@LastModified', 'datetime')							
				  ,t.x.value('@LastModifiedBy', 'varchar(50)')			
				  ,t.x.value('@Version', 'int')	
			from @CustomerDetails.nodes('/CustomerDetails/CustomerDetail') t(x)
			OPTION (OPTIMIZE FOR ( @CustomerDetails = NULL ))
			/* add validation */
			insert into [dbo].[CustomerDetails] select * from @tCustomerDetails t where t.[Name] is not null
			commit transaction tCustomerDetails

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



























GO