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