USP_QUEUEPROCESS_GET_MOST_RECENT_STATUS

CREATE PROCEDURE dbo.USP_QUEUEPROCESS_GET_MOST_RECENT_STATUS

/*
Returns status information about the last time a Queue Process was run.
*/


@QUEUEPROCESSID uniqueidentifier,

@STATUS_AVAILABLE bit=null output,

@STATUSCODE     tinyint = NULL  output
/*
0=Completed
1=Running
2=Did not Finish
3=Results Cleared
*/
,

@STATUS_IS_COMPLETED bit=null output,
@STATUS_IS_RUNNING bit=null output,
@STATUS_IS_DID_NOT_FINISH bit=null output,
@STATUS_IS_RESULTS_CLEARED bit=null output,

@HASEXCEPTIONS bit=null output,

@STARTEDON datetime = null output,

@ENDEDON datetime = null output,

@STATUSTEXT nvarchar(25)= null output,

@SERVERNAME nvarchar(255)=null output,

@LASTMESSAGE nvarchar(max)=null output

as

SET NOCOUNT ON;

SET @STATUS_AVAILABLE=0;
SET @STATUS_IS_COMPLETED=0;
SET @STATUS_IS_RUNNING=0;
SET @STATUS_IS_DID_NOT_FINISH=0;
SET @STATUS_IS_RESULTS_CLEARED=0;
SET @HASEXCEPTIONS=0;

DECLARE @BUSINESSPROCESSSTATUSID uniqueidentifier;


select top 1 
    @BUSINESSPROCESSSTATUSID = QPS.ID

from
    dbo.QUEUEPROCESSSTATUS AS QPS

where 
    QPS.PARAMETERSETID=@QUEUEPROCESSID

order by
    QPS.DATEADDED DESC;


if @BUSINESSPROCESSSTATUSID is null 
    RETURN 0; --no status available



SET @STATUS_IS_RUNNING=CASE WHEN APPLOCK_TEST('public',upper(cast(@BUSINESSPROCESSSTATUSID as nvarchar(36))),'Exclusive','Session')=0 THEN 1 ELSE 0 END;


declare @NUMBEROFEXCEPTIONS int;

SELECT
@STATUS_AVAILABLE=1,
@STATUSCODE=STATUSCODE,
@STATUSTEXT=[STATUS],
@SERVERNAME=SERVERNAME,
@NUMBEROFEXCEPTIONS=NUMBEROFEXCEPTIONS,
@STARTEDON=STARTEDON,
@ENDEDON=@STARTEDON,
@LASTMESSAGE=ERRORMESSAGE
from dbo.BUSINESSPROCESSSTATUS
WHERE ID=@BUSINESSPROCESSSTATUSID;

IF @STATUSCODE=1 AND @STATUS_IS_RUNNING=0 --the status table is not up to date, so override

BEGIN
    SET @STATUSCODE=2;
    SET @STATUSTEXT=N'Did not finish';    
END


IF @NUMBEROFEXCEPTIONS >0 
    SET @HASEXCEPTIONS=1;

IF @STATUSCODE=0
    SET @STATUS_IS_COMPLETED=1;

IF @STATUSCODE=1
    SET @STATUS_IS_RUNNING=0;

IF @STATUSCODE=2
    SET @STATUS_IS_DID_NOT_FINISH=0;

IF @STATUSCODE=3
    SET @STATUS_IS_RESULTS_CLEARED=0;

return 0;