USP_BATCH_GETMAXSEQUENCE

            CREATE procedure dbo.USP_BATCH_GETMAXSEQUENCE
            (
                @BATCHID uniqueidentifier, 
                @RESULT int output
            )
            as
            set nocount on;

            declare @BASETABLENAME nvarchar(128);

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

            set @RESULT = 0;

            if len(@BASETABLENAME) > 0
            begin
                declare @SQL nvarchar(max);
                declare @PARAMS nvarchar(100);

                set @SQL = 'select @RESULT = coalesce(max(SEQUENCE), 0) from dbo.' + @BASETABLENAME + ' where BATCHID = @BATCHID';
                set @PARAMS = '@RESULT int output, @BATCHID uniqueidentifier';

                set @RESULT = 0;
                begin try
                    exec sp_executesql @SQL, @PARAMS, @RESULT = @RESULT output, @BATCHID = @BATCHID
                end try
                begin catch
                    set @RESULT = 0;
                end catch
            end