USE [Framework_REAP-1]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetSurfaceModulePicklist]    Script Date: 2019/12/13 17:16:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-06-14
-- Description:	Gets the data for the Surface Module Picklist
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetSurfaceModulePicklist] 
	@surfaceFieldId INT
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
		SELECT d.surfaceFieldValueChar as DataText, i.recId as DataValue 
		FROM pal_SurfaceItem i
		INNER JOIN pal_SurfaceFieldData d ON d.surfaceItemId = i.recId
		WHERE d.surfaceFieldID = @surfaceFieldId 
		AND i.isActive = 1 AND i.isDeleted = 0 
		ORDER BY DataText
	end;
	else
	begin
		set @table = 'publ_' + @surfaceName;
		declare @query varchar(max);

		set @query = 'select ' + @fieldName + ' as DataText, itemID as DataValue from ' + @table 
			+ ' where isActive = 1 and ' + @fieldName + ' <> '''''
		set @query = @query + ' order by ' + @fieldName

		print @query
		exec (@query)
	end;

    

END
GO


