USP_APPUSER_HASBUSINESSPROCESSTASKBYSTATUSID

/*
Returns true if the given user has been assigned a RunBusinessProcess task that uses the parameter set referenced by the given status ID
*/
create procedure dbo.USP_APPUSER_HASBUSINESSPROCESSTASKBYSTATUSID
    (
        @APPUSERID uniqueidentifier,
        @BUSINESSPROCESSSTATUSID uniqueidentifier,
        @RES bit output
    )
as
    begin
        set @RES = 0

        declare @PARAMETERSETID uniqueidentifier
        exec dbo.USP_BUSINESSPROCESS_GETPARAMETERSETIDFROMSTATUSID @BUSINESSPROCESSSTATUSID, @PARAMETERSETID output

        if @PARAMETERSETID is not null
            if exists (select 
                         top 1 TASKCATALOG.ID
                       from dbo.TASKCATALOG 
                         inner join dbo.SYSTEMROLETASK on TASKCATALOG.ID = SYSTEMROLETASK.TASKID
                         inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLETASK.SYSTEMROLEID
                       where 
                         (dbo.SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID) and
                         TASKSPECXML.value('declare namespace bbtask="bb_appfx_task";
                                            declare namespace common="bb_appfx_commontypes";
                                            (bbtask:TaskSpec/common:RunBusinessProcess/@BusinessProcessParameterSetID)[1]','uniqueidentifier') = @PARAMETERSETID)
                set @RES = 1
            else
                set @RES = 0
    end