USP_BATCH_SYSTEMMESSAGES

CREATE procedure dbo.USP_BATCH_SYSTEMMESSAGES (
    @BATCHID uniqueidentifier, 
    @ROWID uniqueidentifier = null, -- Returns messages for only the specified row. Takes precedence over sequence params.

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

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

    declare @BASETABLENAME nvarchar(200);


    select @BASETABLENAME = BATCHTYPECATALOG.BASETABLENAME
    from dbo.BATCH
    inner join dbo.BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
    inner join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
    where BATCH.ID = @BATCHID

    declare @sql nvarchar(1000);
    declare @paramdef nvarchar(1000);


    set @sql = N'exec dbo.' + quotename('USP_' + @BASETABLENAME + 'BATCHSYSTEMMESSAGES_SELECT') + ' @BATCHID, @ROWID, @STARTSEQUENCE, @SEQUENCECOUNT';
    set @paramdef = N'@BATCHID uniqueidentifier, @ROWID uniqueidentifier, @STARTSEQUENCE int, @SEQUENCECOUNT int';

    declare @messages table (
        SEQUENCE int,  
        ID uniqueidentifier, 
        ROWID uniqueidentifier, 
        MESSAGETEXT nvarchar(2000), 
        MESSAGETYPECODE tinyint, 
        MESSAGETYPE nvarchar(100), 
        INVALIDFIELDID nvarchar(200), 
        INVALIDFIELDVALUE nvarchar(max), 
        SEVERITYCODE tinyint, 
        SEVERITY nvarchar(100),
        ORIGINCODE tinyint, 
        ORIGIN nvarchar(100)
    )


    insert into @messages
    exec sp_executesql @sql, @paramdef, @BATCHID = @BATCHID, @ROWID = @ROWID, @STARTSEQUENCE = @STARTSEQUENCE, @SEQUENCECOUNT = @SEQUENCECOUNT;

    select
        SEQUENCE,  
        ID, 
        ROWID, 
        MESSAGETEXT, 
        MESSAGETYPECODE, 
        MESSAGETYPE, 
        INVALIDFIELDID, 
        INVALIDFIELDVALUE, 
        SEVERITYCODE, 
        SEVERITY, 
        ORIGINCODE, 
        ORIGIN
    from @messages
    order by SEQUENCE

end