Conditional Logic to Retrieve and Update Batch Numbers In SPROC

When executing several processes in my Access 2013 front end with a SQL Server Developer 14 backend, I want to ‘stamp’ the records involved with a batch number in case they need to be pulled together later.
Some of the processes I want to track are:

‘Posting Payments’
‘Sending Letter 1’
‘Sending Letter 2’
‘Sending Letter 4’
‘Sending Final Notice’

I have table ‘tblParmFile’ with a field defined for each of these batch types.

tblParmfile
PostingBatchNumber      Int
Letter1BatchNumber      Int
Letter2BatchNumber      int
Letter3BatchNumber     int

In actuality there are many more types defined in tblParmFile, the above are just examples.

All the fields are on the same tblParmFile record and there is 1 and only 1 tblParmFile record, ever.

I know I can write a simple unique SPROC to retrieve and update each type of batch number but I was wondering if I could do it all in one SPROC using conditional logic based on which batch number is being retrieved and updated.  

The first ting the SPROC would do is update the current batch number.
Then it would return the update batch number

I’m kind of new to SQL and not that familiar with conditional logic.

In Pseudo Codes here’s what I would like to do

Pass the batch type to the SPROC
Return the next Batch number for the Type

Something like this
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetTANextBatchtNum]    Script Date: 11/27/2017 1:49:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetTANextBatchtNum] 
	-- Add the parameters for the stored procedure here
  	@BatchType           int = 0, 
	@NextBatchNumber   int = 0    Output
   
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Case @BatchTYpe =1
	Update tblPamFIle  Set PostingBatchNumber = PostingBatchNumber + 1
		@NextBatchNumber = PostingBatchNumber
Case @BatchTYpe =2
	Update tblPamFIle  Set Letter1BatchNumber      = Letter1BatchNumber      + 1
		@NextBatchNumber = Letter1BatchNumber      
Case @BatchTYpe = 3
	Update tblPamFIle  Set Letter2BatchNumber      = Letter2BatchNumber      + 1
		@NextBatchNumber = Letter2BatchNumber      
Case @BatchTYpe =4
	Update tblPamFIle  Set Letter3BatchNumber      = Letter3BatchNumber      + 1
		@NextBatchNumber = Letter3BatchNumber      


END

Open in new window


Can my bogus code be revised to handle the conditional retrieval and updating of a batch number?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please use this.  You need to use the IF and ELSE IF statements.

USE [JTSConversion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetTANextBatchtNum] 
(	
  	@BatchType  int = 0, 
	@NextBatchNumber  int = 0 Output
)   
AS
BEGIN	
	SET NOCOUNT ON;

IF @BatchTYpe = 1
		Update tblPamFIle  SET PostingBatchNumber = PostingBatchNumber + 1
		@NextBatchNumber = PostingBatchNumber
ELSE IF @BatchTYpe = 2
		Update tblPamFIle  SET Letter1BatchNumber = Letter1BatchNumber + 1
		@NextBatchNumber = Letter1BatchNumber 
ELSE IF @BatchTYpe = 3
		Update tblPamFIle  SET Letter2BatchNumber = Letter2BatchNumber + 1
		@NextBatchNumber = Letter2BatchNumber      
ELSE IF @BatchTYpe =4
		Update tblPamFIle  SET Letter3BatchNumber = Letter3BatchNumber + 1
		@NextBatchNumber = Letter3BatchNumber      

END

Open in new window

mlcktmguyAuthor Commented:
Thanks you.  

I am getting four  'Incorrect syntax near @NextBatchNumber' errors.  These are all within the IF statement on every line

@NextBatchNumber = ....BatchNumber
Pawan KumarDatabase ExpertCommented:
Sorry. Updated. Missed where clause. Added now.

USE [JTSConversion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetTANextBatchtNum] 
(	
  	@BatchType  int = 0, 
	@NextBatchNumber  int = 0 Output
)   
AS
BEGIN	
	SET NOCOUNT ON;

IF @BatchTYpe = 1
		Update tblPamFIle  SET PostingBatchNumber = PostingBatchNumber + 1
		WHERE PostingBatchNumber = @NextBatchNumber
ELSE IF @BatchTYpe = 2
		Update tblPamFIle  SET Letter1BatchNumber = Letter1BatchNumber + 1
		WHERE Letter1BatchNumber = @NextBatchNumber
ELSE IF @BatchTYpe = 3
		Update tblPamFIle  SET Letter2BatchNumber = Letter2BatchNumber + 1
		WHERE Letter2BatchNumber = @NextBatchNumber
ELSE IF @BatchTYpe =4
		Update tblPamFIle  SET Letter3BatchNumber = Letter3BatchNumber + 1
		WHERE Letter3BatchNumber = @NextBatchNumber
END

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mlcktmguyAuthor Commented:
That compiles but returns Null in @NextBatchNumber no matter what the value of @BatchType is.

Remember there is only one record in tblParmfile.  All the fields being selected are on the same record
mlcktmguyAuthor Commented:
It also doesn't update the respective BatchNumber in tblParmFile by 1
Pawan KumarDatabase ExpertCommented:
Do you want to set

@NextBatchNumber = PostingBatchNumber if the batch type =1
@NextBatchNumber = Letter1BatchNumber  if the batch type =2......

Also is there where clause is incorrect ?

see if you are getting value in @NextBatchNumber with below code...
USE [JTSConversion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetTANextBatchtNum] 
(	
  	@BatchType  int = 0, 
	@NextBatchNumber  int = 0 Output
)   
AS
BEGIN	
	SET NOCOUNT ON;

IF @BatchTYpe = 1
		Update tblPamFIle  SET PostingBatchNumber = PostingBatchNumber + 1
		WHERE PostingBatchNumber = @NextBatchNumber
ELSE IF @BatchTYpe = 2
		Update tblPamFIle  SET Letter1BatchNumber = Letter1BatchNumber + 1
		WHERE Letter1BatchNumber = @NextBatchNumber
ELSE IF @BatchTYpe = 3
		Update tblPamFIle  SET Letter2BatchNumber = Letter2BatchNumber + 1
		WHERE Letter2BatchNumber = @NextBatchNumber
ELSE IF @BatchTYpe =4
		Update tblPamFIle  SET Letter3BatchNumber = Letter3BatchNumber + 1
		WHERE Letter3BatchNumber = @NextBatchNumber
SET @NextBatchNumber = @NextBatchNumber
RETURN

END

Open in new window


Also you need to execute a sp like below.
DECLARE @OUTNextbatchNumner INT = 1
EXECUTE [dbo].[spGetTANextBatchtNum] 10 , @NextBatchNumber = @OUTNextbatchNumner OUTPUT
SELECT @OUTNextbatchNumner;

Open in new window

mlcktmguyAuthor Commented:
There is no where clause needed.  The file only contains one record, all of the referenced fields are on the same record.

I created the same logic in MS Access but was thinking that a SPROC might be quicker.  Maybe it can't be done in a SPROC.

Here it the working MS Access version.  Perhaps seeing it will explain what I'm trying to accomplish better.

Public Function getNextBatchNumber(passedBatchProcessID As Long) As Long
'
getNextBatchNumber = 0
'
Dim wkBatchNumFieldName As String

Select Case passedBatchProcessID
'
    Case eBatchProcessType.eLetterBeforeLiening
        wkBatchNumFieldName = "LBLBatchNum"
        '
    Case eBatchProcessType.e30DayLetter
        wkBatchNumFieldName = "ThirtyDayNoticeBatchNum"
        '
    Case eBatchProcessType.e10DayLetter
        wkBatchNumFieldName = "TenDayNoticeBatchNum"
        '
    Case eBatchProcessType.eSciFa
        wkBatchNumFieldName = "SciFaBatchNum"
        '
    Case eBatchProcessType.eImportantNotice
        wkBatchNumFieldName = "ImportantNoticeBatchNum"
        '
    Case eBatchProcessType.eDefaultJudgement
        wkBatchNumFieldName = "JudgementBatchNum"
        '
    Case eBatchProcessType.eFinalNotice
        wkBatchNumFieldName = "FinalNoticeBatchNum"
        '
    Case eBatchProcessType.ePaymentPosting
        wkBatchNumFieldName = "PostingBatchNum"
        '
    Case eBatchProcessType.eCountyPayFIle
        wkBatchNumFieldName = "CountyPayFIlesBatchNumber"
        '
    Case eBatchProcessType.eLIenSubmission
        wkBatchNumFieldName = "LienSubmissionBatchNumber"
        '
    Case eBatchProcessType.eLienReturned
        wkBatchNumFieldName = "LienSubmissionReturnedBatchNumber"
        '
    Case eBatchProcessType.eSatisfactionSubmission
        wkBatchNumFieldName = "SatisfactionBatchNumber"
        '
    Case eBatchProcessType.eTreasurersSale
        wkBatchNumFieldName = "TreasurersSaleBatchNum"
        '
    Case Else
        wkBatchNumFieldName = "WorkID"
        '
End Select


selectString = " Select " & wkBatchNumFieldName & " From tblParmFile "
'Debug.Print selectString
'
Dim rsIn2 As ADODB.Recordset
Set rsIn2 = New ADODB.Recordset
rsIn2.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
If rsIn2.EOF Then
    '
Else
    rsIn2(wkBatchNumFieldName) = Nz(rsIn2(wkBatchNumFieldName)) + 1
    getNextBatchNumber = Nz(rsIn2(wkBatchNumFieldName))
    rsIn2.Update
End If
'
End Function

Open in new window

Pawan KumarDatabase ExpertCommented:
Updated.
Please try.

ALTER PROCEDURE [dbo].[spGetTANextBatchtNum] 
(	
  	@BatchType  int = 0, 
	@NextBatchNumber  int = 0 Output
)   
AS
BEGIN	
SET NOCOUNT ON;
UPDATE tblPamFIle  
SET PostingBatchNumber = CASE WHEN PostingBatchNumber = @NextBatchNumber AND @BatchTYpe = 1 THEN PostingBatchNumber + 1 
						 ELSE PostingBatchNumber END
,   Letter1BatchNumber = CASE WHEN Letter1BatchNumber = @NextBatchNumber AND @BatchTYpe = 2 THEN Letter1BatchNumber + 1
						 ELSE Letter1BatchNumber END
,   Letter2BatchNumber = CASE WHEN Letter2BatchNumber = @NextBatchNumber AND @BatchTYpe = 3 THEN Letter2BatchNumber + 1
						 ELSE Letter2BatchNumber END
,	 Letter3BatchNumber = CASE WHEN Letter3BatchNumber = @NextBatchNumber AND @BatchTYpe = 4 THEN Letter3BatchNumber + 1
						 ELSE Letter3BatchNumber END
END

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
I will check this soon.  I have not had time to evaluate yet.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.