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
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)