DP44/DataPRO_sql/dbo.sp_DBImportGroupSensors.StoredProcedure.sql

<EFBFBD><EFBFBD>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportGroupSensors]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DBImportGroupSensors]
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_DBImportGroupSensors]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportGroupSensors] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_DBImportGroupSensors]
		 @TestObjectId		int
		,@TestObjectName	varchar(50)
		,@Sensors			xml
		,@errorNumber				int				output
		,@errorMessage				nvarchar(250)	output 
AS
BEGIN
	set @errorNumber = 0 
	set @errorMessage = space(0)
	begin try
		begin transaction tTestObjectSensors

		declare @tSensors table (
				SensorId		int 
				, DASId			int 
				, TestObjectId	int 
				, ISOChannel	bit 
				, ISOChannelId	int 
				, ChannelId		int 
				, ChannelIdx	int
				, ZoneId		nvarchar(50) 
				, LocalOnly		bit)

		insert into @tSensors
					(SensorId		
					, DASId			
					, TestObjectId	
					, ISOChannel	
					, ISOChannelId	
					, ChannelId		
					, ChannelIdx
					, ZoneId		
					, LocalOnly)
		select 
					  t.x.value('(@SensorId)', 'int') 
					, t.x.value('(@DASId)', 'int')
					, @TestObjectId
					, t.x.value('(@ISOChannel)', 'bit')		
					, t.x.value('(@ISOChannelId)', 'int')	
					, t.x.value('(@ChannelId)', 'int')		
					, t.x.value('(@ChannelIdx)', 'int')		
					, t.x.value('(@ZoneId)', 'nvarchar(50)')
					, t.x.value('(@LocalOnly)', 'bit')		
					
			from @Sensors.nodes('/Sensors/Sensor') t(x)

		insert into [dbo].[TestObjectSensors]
			([SensorId]
			, [DASId]
			, [TestObjectId]
			, [ISOChannel]
			, [ISOChannelId]
			, [ChannelId]
			, [ZoneId]
			, [LocalOnly]
			, [ChannelIdx])
		select
				SensorId
			, DASId			
			, TestObjectId	
			, ISOChannel	
			, ISOChannelId	
			, ChannelId		
			, ZoneId		
			, LocalOnly		
			, ChannelIdx
		from @tSensors where SensorId != 0 /* or any othe restrictions/validations*/
		commit transaction tTestObjectSensors
	end try
	begin catch
		set @errorNumber	= error_number()
		set @errorMessage	= error_message()
		rollback transaction tTestObjectSensors
	end catch;	
END


























GO