USP_MKTSEGMENTATION_GETBYBUSINESSPROCESSOUTPUTID

CREATE procedure dbo.USP_MKTSEGMENTATION_GETBYBUSINESSPROCESSOUTPUTID
(  
  @BUSINESSPROCESSOUTPUTID uniqueidentifier  
)  
as
begin

    -- Variables used 

    DECLARE @MKTSEGMENTATIONDATATABLENAME AS NVARCHAR(56);
    DECLARE @SQL AS NVARCHAR(max);

    -- Prepare the name of segmentation data table

    SELECT @MKTSEGMENTATIONDATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID])
    FROM dbo.[BUSINESSPROCESSOUTPUT]  
    INNER JOIN dbo.[BUSINESSPROCESSSTATUS] ON [BUSINESSPROCESSSTATUS].[ID] = [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID]  
    INNER JOIN dbo.[MKTSEGMENTATIONEXPORTPROCESS] ON [MKTSEGMENTATIONEXPORTPROCESS].[ID] = [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID]  
    WHERE [BUSINESSPROCESSOUTPUT].[ID] = @BUSINESSPROCESSOUTPUTID;

    -- Get the segmentation details

    SET @SQL = 'SELECT ISNULL(S.NAME,'''') FROM dbo.[' + @MKTSEGMENTATIONDATATABLENAME + '] SD INNER JOIN MKTSEGMENTATIONSEGMENT SS ON SD.SEGMENTID = SS.ID' + CHAR(13) +
            'INNER JOIN MKTSEGMENTATION S ON S.ID = SS.SEGMENTATIONID;' + CHAR(13);

    -- executing the prepared query        

    EXEC SP_EXECUTESQL @SQL; 

end