A Stored Procedure to generate a random token

You may have encountered a scenario where you want to generate a random token for identity verification purposes. In this article I tried to include a stored procedure in which you can easily generate a random token to be sent to a user to enter it and verify themselves. For this scenario you’ll need two tables one to store the new generated token, call it dbo.token and the other to store the already generated and used token, i.e dbo.tokenUsed so that a user cannot use it again. Remember that your tables needs to contain TokenID, Token, TokenStatus (Active, Inactive) etc – so that your token will be valid for sometime only, NOT forever.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspRandomToken] 
(
    @length INT,
    @output BIGINT OUTPUT
)
AS
BEGIN  
    SET NOCOUNT ON;  
	BEGIN TRY
    SET @output = ''
       
    DECLARE @RandomToken BIGINT, 
            @UpperCase BIGINT, 
            @LowerCase BIGINT

    SET @LowerCase = CAST(REPLICATE('1',@length ) AS BIGINT) 
    SET @UpperCase = CAST(REPLICATE('9',@length ) AS BIGINT)    

       DECLARE @Count TINYINT;
       SET @Count = 0;

       WHILE @Count < 100 
	   BEGIN
              SELECT @Random = ROUND(((@UpperCase - @LowerCase -1) * RAND() + @LowerCase), 0)

IF NOT EXISTS(
SELECT * FROM [dbo].[tokenUsed] WHERE Token = CAST(@RandomToken AS INT))
              BEGIN
                 SET @output = @Random
Token
                 BREAK;
              END
              ELSE
              BEGIN
                SET @Count =  @Count + 1
              END
  END;
END TRY
	
BEGIN CATCH
 SELECT -1 AS Result,'Error Line: ' + CAST(ERROR_LINE() AS      VARCHAR(10)) + '; ' + 'Error Message: ' + ERROR_MESSAGE() AS Message
END CATCH
END
GO