How to use a counter that won't give the same number to different records

Hello,

I have the following SPROC that gets the application number from a counter. How can I make sure that two records won't get the same appno from the counter?

CREATE PROCEDURE dbo.ApplicationInsert 
(@IDNO nvarchar(20),
 @FirstName nvarchar(50)=null,
 @LastName nvarchar(50)=null,
 @AppNo int OUTPUT
 )
AS
BEGIN
	DECLARE @ApplicantID int;
	DECLARE @ApplCounter int;

	SELECT @ApplCounter=ApplCounter FROM tblParameters
	WHERE CounterYear=CONVERT(NVARCHAR(4),YEAR(GETDATE()))

	INSERT INTO tblApplicant 
	  (IDNO,FirstName,LastName)
	VALUES                       
	  (@IDNO,@FirstName,@LastName)

SET @ApplicantID= SCOPE_IDENTITY()


 INSERT INTO tblApplication(Appno,ApplicantID,IDNO)
 VALUES ((@ApplCounter+1),@ApplicantID,@IDNO)

UPDATE tblParameters SET
       ApplCounter=@ApplCounter+1

SELECT @AppNo=@ApplCounter+1
END

Thank you in advance.

SELECT @ApplCounter=ApplCounter FROM tblParameters
	WHERE CounterYear=CONVERT(NVARCHAR(4),YEAR(GETDATE()))
...
UPDATE tblParameters SET
       ApplCounter=@ApplCounter+1

This is incrementing ALL rows in [tblParameters], not just the one for the current CounterYear. Is that intentional?

If not then I would do this:

replace

SELECT @ApplCounter=ApplCounter FROM tblParameters
	WHERE CounterYear=CONVERT(NVARCHAR(4),YEAR(GETDATE()))

with

UPDATE U
SET @ApplCounter=ApplCounter,
       ApplCounter=@ApplCounter+1
FROM tblParameters AS U
	WHERE CounterYear=CONVERT(NVARCHAR(4),YEAR(GETDATE()))

and remove the later

UPDATE tblParameters SET
       ApplCounter=@ApplCounter+1

completely.

I'd want a TRANSACTION in the SProc too - so that an error part way through would not allow, for example, a new row to be added to [tblApplicant] WITHOUT a corresponding row in [tblApplication]

You could include the UPDATe of [tblParameters] in the transaction (which would insure that was not incremented unless the pair of Applicant/Application inserts did not also happen, but that might also lead to blocking)

Thank you for the answer.
I added the TRANSACTION too, it makes sense :slight_smile: