Click here to Skip to main content
14,981,116 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
After 1000 this will reset counter for specific term to 1 again. Will this stored procedure be 100% reliable to give unique id every time. There are multiple users accessing the web application that may call this stored procedure.

SQL
ALTER PROCEDURE [dbo].[get_next_id](@Term int,@ID INT OUTPUT) AS
SET NOCOUNT ON
DECLARE @Current int;
SELECT @Current=[ID] FROM [IdGenerator] where Term = @Term;
IF @Current<1000
BEGIN
    update IdGenerator SET @ID = ID, ID = ID + 1 where Term=@Term
END
ELSE
BEGIN
    update IdGenerator SET @ID =1,ID = 2 where Term=@Term
END



One option is to use identity column but that is out of question as to generate unique Id I have to add certain text to each number as that is the part of business requirement. Like add 1001 or 1002 or 1003 etc. in front of numbers. This stored procedure will give me the unique number.
Criticize it / Suggest any alternative .
Posted
Comments
R. Giskard Reventlov 16-Sep-10 2:37am
   
I've scored this 1 since you've already asked the question in another forum: DO NOT CROSS-POST!

Difficult to advise, as you have not said what the Id is used for.

If it is for a record Id then there is a probability that the resulting Id will NOT be unique and secondly I think that I would have used a Insert trigger on the tables to generate the UID, thus ensuring the UID is always generated.
   
Comments
virang_21 16-Sep-10 7:15am
   
I am generating studentid using this stored proc. The business logic for studentid is different . It has first two digit of year and next two digit is from 01 to 08 indicating term no and next three digit is sequential number . So for student starting in term 01of year 2010 will have id like 1001xxx. I am using stored proc to generate xxx part of the studentid number . I have to make sure that studentid is unique for each student .
What you are doing is fine, but I would still consider using a INSERT trigger on the table, as this will always fire when a new student is added, no matter which program/source inserted the data.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900