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:
‘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.
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
/****** Object: StoredProcedure [dbo].[spGetTANextBatchtNum] Script Date: 11/27/2017 1:49:35 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spGetTANextBatchtNum]
-- Add the parameters for the stored procedure here
@BatchType int = 0,
@NextBatchNumber int = 0 Output
-- 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
Can my bogus code be revised to handle the conditional retrieval and updating of a batch number?