Is this unique ID generation procedure correct ?
-
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.
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
ENDOne 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 .
-
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.
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
ENDOne 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 .
Criticise - not a problem this is a dumb idea. Record identifiers are just that they identify the record, the business has no input into how they are created, maintained of contain. On the other hand a code used by the users to identify a record is completely up to the business and they can ask for any rules they like Why are you resetting after 1000 and how can that be unique? Suggest - also not a problem Have 2 fields, recordID which is an identity field and record code which store the business defined identifier.
Never underestimate the power of human stupidity RAH
-
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.
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
ENDOne 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 .
Mycroft has given you some good advice. In terms of your original question, yes this will be reliable if you serialize the transactions. Incidentally, this will reset before 1000, not after. I assume that is what you want, so you are generating your business numbers in the range 1001... to 1999...