USP_EXCHANGEDOWNLOADPROCESS_GETBATCHID

Returns the first valid batch id for the given batch owner and batch template id.
Parameters

Parameter	Parameter Type	Mode
@MAXROWCOUNT	int	IN
@BATCHTEMPLATEID	uniqueidentifier	IN
@OWNERID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_EXCHANGEDOWNLOADPROCESS_GETBATCHID
            (
                @MAXROWCOUNT int,
                @BATCHTEMPLATEID uniqueidentifier,
                @OWNERID uniqueidentifier
            )
            as
                set nocount on;    

                declare @BASETABLENAME nvarchar(128);
                declare @SQL nvarchar(830);        

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

                set @SQL = 'select top 1 
                                @BATCHID_OUT = BATCH.ID, 
                                @COUNT_OUT = (select count(BATCHTABLE.ID) from dbo.' + @BASETABLENAME + ' as BATCHTABLE where BATCHTABLE.BATCHID = BATCH.ID) 
                            from 
                                dbo.BATCH 
                                inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
                                inner join dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID
                            where 
                                coalesce((select count(BATCHTABLE.ID) from dbo.' + @BASETABLENAME + ' as BATCHTABLE where BATCHTABLE.BATCHID = BATCH.ID), 0) < @MAXROWCOUNT_IN
                                and BATCHTEMPLATE.ID = @BATCHTEMPLATEID_IN
                                and BATCHWORKFLOWSTATE.ISINITIALSTATE = 1
                                and BATCH.APPUSERID = @OWNERID_IN
                                and BATCH.STATUSCODE = 0';

                declare @PARAMETERS nvarchar(150);
                set @PARAMETERS = '@BATCHTEMPLATEID_IN uniqueidentifier, @MAXROWCOUNT_IN int, @OWNERID_IN uniqueidentifier, @BATCHID_OUT uniqueidentifier output, @COUNT_OUT int output';

                declare @BATCHID uniqueidentifier
                declare @COUNT int

                exec sp_executesql @SQL, @PARAMETERS, @BATCHTEMPLATEID_IN = @BATCHTEMPLATEID, @MAXROWCOUNT_IN = @MAXROWCOUNT, @OWNERID_IN = @OWNERID, @BATCHID_OUT = @BATCHID output, @COUNT_OUT = @COUNT output;    

                -- Have to do this so that StoredProcGen will be able to pick up return columns

                select @BATCHID as BATCHID, @COUNT as COUNT