USP_BUSINESSPROCESS_GETPARAMETERSETIDFROMSTATUSID

/*
returns the parameter set ID associated with the given status ID
*/
create procedure dbo.USP_BUSINESSPROCESS_GETPARAMETERSETIDFROMSTATUSID(
    @BUSINESSPROCESSSTATUSID uniqueidentifier,
    @PARAMETERSETID uniqueidentifier output
)
as
    declare @STATUSTABLENAME nvarchar(106)

    select @STATUSTABLENAME = BP.PARAMETERTABLENAME + 'STATUS' 
    from dbo.BUSINESSPROCESSCATALOG as BP inner join dbo.BUSINESSPROCESSSTATUS as BPS on BP.ID = BPS.BUSINESSPROCESSCATALOGID
    where BPS.ID = @BUSINESSPROCESSSTATUSID

    if @STATUSTABLENAME is not null
        begin
            declare @SQL nvarchar(500)
            set @SQL = N'select @P = PARAMETERSETID from ' + @STATUSTABLENAME + ' where ID = ' + '''' + cast(@BUSINESSPROCESSSTATUSID as nvarchar(36)) + ''''

            declare @PARAMDEF nvarchar(500)
            set @PARAMDEF = N'@P uniqueidentifier output'
            exec sp_executesql @SQL, @PARAMDEF, @P = @PARAMETERSETID output
        end