USP_BBNC_GETBATCHFORMAPPINGRULE

            create procedure dbo.USP_BBNC_GETBATCHFORMAPPINGRULE 
            (
                @BATCHTEMPLATEID uniqueidentifier,
                @MAPPINGRULEID uniqueidentifier,
                @OWNERID uniqueidentifier,
                @MAXTRANSACTIONS int
            )
            with execute as owner
            as 
                set nocount on;

                declare @TABLENAME nvarchar(128);
                declare @SQL nvarchar(max);
                declare @PARMS nvarchar(max);

                select top (1)
                    @TABLENAME = BATCHTYPECATALOG.BASETABLENAME 
                from
                    dbo.BATCHTYPECATALOG
                    inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
                where
                    BATCHTEMPLATE.ID = @BATCHTEMPLATEID
                order by
                    BATCHTEMPLATE.DATEADDED;

                if not @TABLENAME is null
                begin
                    set @SQL =
                        N'select top (1) BATCH.ID as [BATCHID], ' +
                        N'(select count(ID) from dbo.' + @TABLENAME + ' where ' + @TABLENAME + '.BATCHID = BATCH.ID) as [COUNT] ' +
                        N'from dbo.BATCH inner join dbo.NETCOMMUNITYMAPPINGRULEBATCH on NETCOMMUNITYMAPPINGRULEBATCH.BATCHID = BATCH.ID ' +
                        N'inner join dbo.BATCHWORKFLOWSTATE on BATCH.BATCHWORKFLOWSTATEID = BATCHWORKFLOWSTATE.ID ' +
                        N'where NETCOMMUNITYMAPPINGRULEBATCH.MAPPINGRULEID = @MAPPINGRULEID ' +
                        N'and BATCH.STATUSCODE = 0 and BATCH.ORIGINATINGBATCHID is null and BATCHWORKFLOWSTATE.ISINITIALSTATE = 1 ' +
                        N'and BATCH.APPUSERID = @OWNERID ' +
                        N'and (select count(ID) from dbo.' + @TABLENAME + ' where ' + @TABLENAME + '.BATCHID = BATCH.ID) < @MAXTRANSACTIONS ' +
                        N'and APPLOCK_TEST(''public'', upper(BATCH.ID), ''Exclusive'', ''Session'') = 1';
                    set @PARMS = N'@OWNERID uniqueidentifier, @MAXTRANSACTIONS int, @MAPPINGRULEID uniqueidentifier';
                    exec sp_executesql @SQL, @PARMS, @OWNERID = @OWNERID, @MAXTRANSACTIONS = @MAXTRANSACTIONS, @MAPPINGRULEID = @MAPPINGRULEID;
                end
                else
                    select cast(null as uniqueidentifier) as [BATCHID], 0 as [COUNT];