DP44/DataPRO_sql/dbo.sp_TestChannelSettingsGet.StoredProcedure.sql

<EFBFBD><EFBFBD>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TestChannelSettingsGet]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TestChannelSettingsGet]
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_TestChannelSettingsGet]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_TestChannelSettingsGet] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_TestChannelSettingsGet]
			 @TestSetupName		nvarchar(255) = null
			,@TestObjectName	nvarchar(255) = null
			,@ChannelId			nvarchar(255) = null
AS
BEGIN
	SET NOCOUNT ON;
		declare @TestSetupId			int
		declare @TestObjectId			int

		set @TestSetupId = dbo.foo_IdGetTestSetup(@TestSetupName)
		set @TestObjectId	= dbo.foo_IdGetTestObject(@TestObjectName)		

		SELECT ts.TestSetupName	as [TestName]
			  ,tobj.TestObjectName	as [TestObjectName]
			  ,tcs.[ChannelId]
			  ,tcs.[Setting]
			  ,ssn.SerialNumber [SensorSerial]
			  ,tcs.[Disabled]
		  FROM [dbo].[TestChannelSettings] tcs
		  inner join [dbo].[TestSetups] ts on ts.TestSetupId = tcs.TestSetupId
		  inner join [dbo].[TestObjects] tobj on tobj.TestObjectId = tcs.TestObjectId
		  inner join [dbo].[v_SensorSerialNumber] ssn on ssn.SensorId = tcs.SensorId
		  where		@TestSetupName	is null or (tcs.TestSetupId = @TestSetupId)
				and @TestObjectName is null or (tcs.TestObjectId = @TestObjectId)
				and @ChannelId		is null or (tcs.ChannelId = @ChannelId)

END






























GO