USP_BBNC_GETBATCHBBNCTRANIDS_2_0

            CREATE procedure [dbo].[USP_BBNC_GETBATCHBBNCTRANIDS_2_0](@TRANSACTIONTYPEID varchar(40))
            with execute as owner
            as

            begin
                declare @TABLENAME nvarchar(128);
                declare @SQL nvarchar(300);
        declare @BATCHTYPECATALOGID uniqueidentifier = null;

        SET @BATCHTYPECATALOGID = (  
            CASE @TRANSACTIONTYPEID  
                WHEN '{5705B543-4033-4A3A-BBCD-A731403EE1E6}' THEN '326C43A6-D162-4FD4-8D61-FEF9A0EE8C5E' --donation (ERB)

                WHEN '{172A5D9A-5241-493C-A2C2-EAC164C095B0}' THEN 'C66C39BC-ACD1-4C30-98DB-C5BA8F90EBEC' --event  

                WHEN '{D7D6143F-823D-4c74-AC2F-947CC96B7008}' THEN '196A2540-005A-4547-91A7-B301C464E28C' --membership (membership dues)

                WHEN '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}' THEN '877DBC83-98F9-4008-98A0-902FDB35E819' --signup (CUB)

                WHEN '{DCEAFCC7-0290-488E-A194-953DE66D6AAB}' THEN '98208E65-8334-4D3A-8496-4175352F1430' -- profileupdate  

            END)  

                select top 1 @TABLENAME = BATCHTYPECATALOG.BASETABLENAME
          from dbo.BATCHTYPECATALOG 
                    where ID = @BATCHTYPECATALOGID
                    order by DATEADDED;


        if not @TABLENAME is null 
            begin
            if @TABLENAME = 'BATCHREVENUE' or @TABLENAME = 'BATCHCONSTITUENTUPDATE' or @TABLENAME = 'BATCHMEMBERSHIPDUES'
              begin
                declare @BBNCTABLENAME nvarchar(150);
                set @BBNCTABLENAME = @TABLENAME + N'BBNCINFO';

                declare @FKID nvarchar(250);
                set @FKID = @TABLENAME + N'ID';

                set @SQL = N'select BBNCTRANID from dbo.' + @BBNCTABLENAME + N' inner join dbo.' + @TABLENAME + N' on ' + @BBNCTABLENAME + '.' + @FKID +
                  N' = ' + @TABLENAME + N'.ID inner join dbo.BATCH on ' + @TABLENAME + N'.BATCHID = BATCH.ID';
                set @SQL = @SQL + N' where BATCH.STATUSCODE = 0';
              end
            else
                  begin
                set @SQL = N'select BBNCTRANID from dbo.' + @TABLENAME + N' inner join dbo.BATCH on ' + @TABLENAME + N'.BATCHID = BATCH.ID ';
                  set @SQL = @SQL + N' where BATCH.STATUSCODE = 0';
              end
              exec sp_executesql @SQL;
            end
        else
            select 0 as BBNCTRANID from dbo.BATCH where ID = '00000000-0000-0000-0000-000000000000'
    end