Unique ID generation stored procdeure .

See more:

SQL

SQL-Server

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