USE [Framework_REAP-1]
GO

/****** Object:  StoredProcedure [dbo].[sp_REAP1_GetSurfaceModulePicklist]    Script Date: 2019/12/13 16:53:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[sp_REAP1_GetSurfaceModulePicklist] 
	@surfaceFieldId INT,
	@filter varchar(max) = ''
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @fieldName varchar(150), @surfaceName varchar(150), @isPublished bit, @table varchar(max);

	select @surfaceName = name, @isPublished = isPublished, @fieldName = sf.surfaceFieldName from pal_SurfaceField sf
	join pal_surface s on s.recId = sf.surfaceId
	where sf.recId = @surfaceFieldId;

	if @isPublished <> 1
	begin
		set @table = 'qt_' + @surfaceName;
	end;
	else
	begin
		set @table = 'publ_' + @surfaceName;
	end;

	declare @query varchar(max);

	set @query = 'select distinct ' + @fieldName + ' as DataText from ' + @table + ' where isActive = 1 and ' + @fieldName + ' <> '''''
	if (@filter <> '')
		set @query = @query + ' and ' + @filter

	set @query = @query + ' order by ' + @fieldName

	print @query
    exec (@query)
END
GO


