USP_BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES_SELECT

/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:29:15 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES_SELECT (
    @BATCHID uniqueidentifier, 
    @ROWID uniqueidentifier = null, -- Returns messages for only the specified row. Takes precedence over sequence params.

    @STARTSEQUENCE int = null, -- Returns messages for a range of rows. ROWID must be null.

    @SEQUENCECOUNT int = null
)
as
begin
    set nocount on;


    if not @ROWID is null  -- select only messages for the specified row


        select 
            BATCHRE7INTEGRATIONGIVINGSUMMARY.SEQUENCE, 
            BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES.ID, 
            BATCHRE7INTEGRATIONGIVINGSUMMARYID as ROWID, 
            MESSAGETEXT, 
            MESSAGETYPECODE, 
            MESSAGETYPE, 
            INVALIDFIELDID, 
            INVALIDFIELDVALUE, 
            SEVERITYCODE, 
            SEVERITY, 
            ORIGINCODE, 
            ORIGIN
        from dbo.BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES
        inner join dbo.BATCHRE7INTEGRATIONGIVINGSUMMARY on BATCHRE7INTEGRATIONGIVINGSUMMARY.ID = BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES.BATCHRE7INTEGRATIONGIVINGSUMMARYID
        where BATCHRE7INTEGRATIONGIVINGSUMMARYID = @ROWID


    else
    begin

        if not @STARTSEQUENCE is null -- select messages only for the specified range of rows


            with BATCHROWS as (
                select ID, 
                ROW_NUMBER() over (order by SEQUENCE) as RowNum,
                SEQUENCE
                from dbo.BATCHRE7INTEGRATIONGIVINGSUMMARY
                where BATCHID = @BATCHID
            )
            select 
                BATCHROWS.SEQUENCE, 
                BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES.ID, 
                BATCHRE7INTEGRATIONGIVINGSUMMARYID as ROWID, 
                MESSAGETEXT,
                MESSAGETYPECODE,
                MESSAGETYPE, 
                INVALIDFIELDID, 
                INVALIDFIELDVALUE, 
                SEVERITYCODE, 
                SEVERITY, 
                ORIGINCODE, 
                ORIGIN 
            from dbo.BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES
            inner join BATCHROWS on BATCHROWS.ID =  BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES.BATCHRE7INTEGRATIONGIVINGSUMMARYID
            where RowNum >= @STARTSEQUENCE    and (@SEQUENCECOUNT is null or RowNum < @STARTSEQUENCE + @SEQUENCECOUNT)
            order by SEQUENCE


        else  -- select messages for all rows in the batch


            select 
                BATCHRE7INTEGRATIONGIVINGSUMMARY.SEQUENCE,  
                BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES.ID, 
                BATCHRE7INTEGRATIONGIVINGSUMMARYID as ROWID, 
                MESSAGETEXT, 
                MESSAGETYPECODE, 
                MESSAGETYPE, 
                INVALIDFIELDID, 
                INVALIDFIELDVALUE, 
                SEVERITYCODE, 
                SEVERITY, 
                ORIGINCODE, 
                ORIGIN 
            from dbo.BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES 
            inner join dbo.BATCHRE7INTEGRATIONGIVINGSUMMARY on BATCHRE7INTEGRATIONGIVINGSUMMARY.ID =  BATCHRE7INTEGRATIONGIVINGSUMMARYBATCHSYSTEMMESSAGES.BATCHRE7INTEGRATIONGIVINGSUMMARYID
            where BATCHRE7INTEGRATIONGIVINGSUMMARY.BATCHID = @BATCHID
            order by SEQUENCE

    end

end