DP44/DataPRO_sql/dbo.sp_LevelTriggersInsert.StoredProcedure.sql

<EFBFBD><EFBFBD>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LevelTriggersInsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LevelTriggersInsert]
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_LevelTriggersInsert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_LevelTriggersInsert] AS' 
END
GO

ALTER PROCEDURE [dbo].[sp_LevelTriggersInsert]
		    @TestSetupName			nvarchar(255)
           ,@GroupSerialNumber		nvarchar(255)
		   ,@TestObjectChannelId	nvarchar(255)
           ,@HardwareChannelId		nvarchar(255)
           ,@SensorSerialNumber		nvarchar(255)
           ,@GreaterThanEnabled		bit
		   ,@GreaterThanEU			float   = 0
		   ,@LessThanEnabled		bit
		   ,@LessThanEU				float	= 0
		   ,@InsideUpperEU			float	= 0
		   ,@InsideLowerEU			float	= 0
           ,@OutsideUpperEU			float   = 0
           ,@OutsideLowerEU			float   = 0
           ,@TriggerInside			bit
           ,@TriggerOutside			bit
		   ,@new_id					int output
		   ,@errorNumber			int				output
		   ,@errorMessage			nvarchar(250)	output 

AS
BEGIN
	set @errorNumber = 0 
	set @errorMessage = space(0)

	SET NOCOUNT ON;
		SET NOCOUNT ON;
		declare @TestSetupId	int
		declare @TestObjectId	int
		declare @DASId			int
		declare @SensorId		int
		declare @ChannelId		int
		declare @MMEChannel bit
		declare @MMEChannelId int

		set @TestSetupId = [dbo].[foo_IdGetTestSetup](@TestSetupName)
		set @DASId = [dbo].[foo_IdGetDAS](left(@HardwareChannelId, charindex('_', @HardwareChannelId)-1))
		set @SensorId = [dbo].[foo_IdGetSensor](@SensorSerialNumber)
		set @TestObjectId = [dbo].[foo_IdGetTestObject](@GroupSerialNumber)

		set @ChannelId = right(@HardwareChannelId, LEN(@HardwareChannelId) -  charindex('x', @HardwareChannelId)) 
		set @MMEChannel = left(replace(@TestObjectChannelId, @GroupSerialNumber + '_', space(0)), 1)
		set @MMEChannelId = substring(@TestObjectChannelId, LEN(@TestObjectChannelId) -  charindex('_', reverse(@TestObjectChannelId)) +2, charindex('_', reverse(@TestObjectChannelId))+1)	


		insert into [dbo].[LevelTriggers]
           ([TestSetupId]
           ,[TestObjectId]
           ,[DASId]
           ,[MMEChannel]
           ,[MMEChannelId]
           ,[HardwareChannelId]
           ,[SensorId]
           ,[GreaterThanEnabled]
           ,[GreaterThanEU]
           ,[LessThanEnabled]
           ,[LessThanEU]
           ,[InsideUpperEU]
           ,[InsideLowerEU]
           ,[OutsideUpperEU]
           ,[OutsideLowerEU]
           ,[TriggerInside]
           ,[TriggerOutside])
			values
            (@TestSetupId			
			,@TestObjectId			
			,@DASId					
			,@MMEChannel		
			,@MMEChannelId
			,@ChannelId
			,@SensorId				
			,@GreaterThanEnabled		
			,@GreaterThanEU			
			,@LessThanEnabled		
			,@LessThanEU				
			,@InsideUpperEU			
			,@InsideLowerEU			
			,@OutsideUpperEU			
			,@OutsideLowerEU			
			,@TriggerInside			
			,@TriggerOutside)
		
		set @new_id = scope_identity();
		if(@@error != 0)
		begin
			set @errorNumber = error_number()
			set @errorMessage = error_message()
		end
end			













GO