USP_REPORT_STEWARDSHIPPLANASSIGNMENTPROCESS_PARAMETERS

            create procedure dbo.USP_REPORT_STEWARDSHIPPLANASSIGNMENTPROCESS_PARAMETERS
            (
                @BUSINESSPROCESSSTATUSID uniqueidentifier
            )
            with execute as owner
            as
            begin
                declare @STARTEDON datetime, @PARAMETERSETID uniqueidentifier;
                select 
                    @STARTEDON = STARTEDON,
                    @PARAMETERSETID = BUSINESSPROCESSPARAMETERSETID
                from dbo.BUSINESSPROCESSSTATUS
                where ID = @BUSINESSPROCESSSTATUSID;

                declare @NAME nvarchar(100);
                select @NAME = NAME
                from dbo.STEWARDSHIPPLANASSIGNMENTPROCESS
                where ID = @PARAMETERSETID;

                declare @TABLENAME nvarchar(256);
                select @TABLENAME = TABLENAME from dbo.BUSINESSPROCESSOUTPUT
                where BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID and upper(TABLEKEY) = 'PARAMETERS';

                declare @SQLTOEXEC nvarchar(max);
                set @SQLTOEXEC = '    select top 1
                                        @NAME as NAME, 
                                        PARAMETERS.PROCESSTYPECODE,
                                        -- Prefer to use the record''s current value in case the name changed

                                        -- but use the cached name in case the record doesn'' exist anymore

                                        coalesce(IDSETREGISTER.NAME, PARAMETERS.PLANSELECTIONNAME) as PLANSELECTIONNAME, 
                                        coalesce(SOURCE.NAME, PARAMETERS.OLDOFFICERNAME) as OLDOFFICERNAME, 
                                        coalesce(DESTINATION.NAME, PARAMETERS.NEWOFFICERNAME) as NEWOFFICERNAME,
                                        PARAMETERS.TRANSFERPLANMANAGERASSIGNMENTS, 
                                        PARAMETERS.TRANSFERPLANSTEWARDASSIGNMENTS, 
                                        PARAMETERS.TRANSFERASSIGNEDSTEPRESPONSIBILITIES,
                                        @STARTEDON as STARTEDON
                                    from dbo.' + @TABLENAME + ' as PARAMETERS
                                    left join dbo.IDSETREGISTER on IDSETREGISTER.ID = PARAMETERS.PLANSELECTIONID
                                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PARAMETERS.OLDOFFICERID) SOURCE
                                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PARAMETERS.NEWOFFICERID) DESTINATION'

                exec sp_executesql @SQLTOEXEC,N'@STARTEDON datetime,@NAME nvarchar(100)',@STARTEDON=@STARTEDON,@NAME=@NAME;
            end