USP_DATAFORMTEMPLATE_VIEW_BATCHTICKETPRINTJOBPRINTING

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BATCHTICKETPRINTJOBPRINTING (
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TICKETCOUNT int = null output,
    @PRINTERNAME nvarchar(255) = null output,
    @DOCUMENTID uniqueidentifier = null output,
    @DOCUMENTTYPECODE tinyint = null output,
    @GROUPBYCODE tinyint = null output,
    @TICKETS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @TICKETCOUNT = (select count(*) from dbo.BATCHTICKETPRINTJOBTICKETLINK where BATCHTICKETPRINTJOBTICKETLINK.BATCHTICKETPRINTJOBID = BATCHTICKETPRINTJOB.ID),
        @PRINTERNAME = BATCHTICKETPRINTJOB.PRINTERNAME,
        @DOCUMENTID = BATCHTICKETPRINTJOB.DOCUMENTID,
        @GROUPBYCODE = BATCHTICKETPRINTJOB.GROUPBYCODE,
        @DOCUMENTTYPECODE = DOCUMENT.TYPECODE
    from
        dbo.BATCHTICKETPRINTJOB
    left outer join
        dbo.DOCUMENT on DOCUMENT.ID = BATCHTICKETPRINTJOB.DOCUMENTID
    where
        BATCHTICKETPRINTJOB.ID = @ID;

    if @DATALOADED = 1
    begin
        set @TICKETS = (
            select
                TICKETPRINTINFO.ID as TICKETPRINTINFOID,
                isnull(SALESORDER.RECIPIENTID, SALESORDER.CONSTITUENTID) as CONSTITUENTID,
                SALESORDERITEM.SALESORDERID,
                TICKET.PROGRAMID,
                TICKET.EVENTID,
                FIRSTPROGRAMDOCUMENT.DOCUMENTID
            from
                dbo.BATCHTICKETPRINTJOBTICKETLINK
            inner join
                dbo.TICKETPRINTINFO on TICKETPRINTINFO.ID = BATCHTICKETPRINTJOBTICKETLINK.ID
            inner join
                dbo.TICKET on TICKET.ID = TICKETPRINTINFO.TICKETID
            inner join
                dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
            inner join
                dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
            outer apply (
                select top 1
                    PROGRAMDOCUMENT.DOCUMENTID
                from
                    dbo.PROGRAMDOCUMENT
                inner join
                    dbo.DOCUMENT on DOCUMENT.ID = PROGRAMDOCUMENT.DOCUMENTID
                where
                    DOCUMENT.ISACTIVE = 1
                    and PROGRAMDOCUMENT.PROGRAMID = TICKET.PROGRAMID
                order by
                    PROGRAMDOCUMENT.SEQUENCE
            ) as FIRSTPROGRAMDOCUMENT
            where
                BATCHTICKETPRINTJOBTICKETLINK.BATCHTICKETPRINTJOBID = @ID
            order by
                TICKETPRINTINFO.SEQUENCEID
            for xml raw('ITEM'), type, elements, root('TICKETS'), binary base64
        );
    end

    return 0;