USP_DATAFORMTEMPLATE_VIEW_BUSINESSPROCESSOWNER

create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BUSINESSPROCESSOWNER
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PROCESSINSTANCENAME nvarchar(max) = null output,
    @OWNERNAME nvarchar(max) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @PARAMETERTABLENAME nvarchar(100) = null;
    declare @PARAMETERFIELDNAME nvarchar(100) = null;
    declare @PARAMETERSETID uniqueidentifier = null;

    select
        @DATALOADED = 1,
        @PARAMETERTABLENAME = BUSINESSPROCESSCATALOG.PARAMETERTABLENAME,
        @PARAMETERFIELDNAME = BUSINESSPROCESSCATALOG.PARAMETERLABELFIELD,
        @PARAMETERSETID = BUSINESSPROCESSINSTANCE.BUSINESSPROCESSPARAMETERSETID,
        @OWNERNAME = case when APPUSER.DISPLAYNAME = '' then APPUSER.USERNAME else APPUSER.DISPLAYNAME end
    from
        dbo.BUSINESSPROCESSINSTANCE
        inner join dbo.BUSINESSPROCESSCATALOG on BUSINESSPROCESSCATALOG.ID = BUSINESSPROCESSINSTANCE.BUSINESSPROCESSCATALOGID
        left join dbo.APPUSER on APPUSER.ID = BUSINESSPROCESSINSTANCE.OWNERID
    where
        BUSINESSPROCESSINSTANCE.ID = @ID;

    if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = @PARAMETERFIELDNAME and TABLE_NAME = @PARAMETERTABLENAME)
    begin

        declare @SQL nvarchar(max) = 
'select
    @PROCESSINSTANCENAME = ' + @PARAMETERFIELDNAME + '
from
    dbo.[' + @PARAMETERTABLENAME + ']
where
    ID = @PARAMETERSETID;'

        execute sp_executesql @SQL, N'@PARAMETERSETID uniqueidentifier, @PROCESSINSTANCENAME nvarchar(max) output', @PARAMETERSETID=@PARAMETERSETID, @PROCESSINSTANCENAME=@PROCESSINSTANCENAME output;

    end

    if @PROCESSINSTANCENAME is null
        set @PROCESSINSTANCENAME = N'';

    return 0;