Technical Article

Random string and random number generators

,

The first part will create 2 stored procedures with self-explanatory names. The second is a standalone SQL that may be used in any stored procedure, script or DTS package.
The generated string does not have any spaces and include only lower-case letters. To include anything else just change the boundaries for ASCII codes in the script.
The RandomString procedure requires the string length as a parameter while RandomNumber needs the lower and upper boundaries as parameters.

-- Part 1 - SPs creation
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'RandomString' and type = 'P')
DROP PROC RandomString
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'RandomNumber' and type = 'P')
DROP PROC RandomNumber
GO

CREATE PROCEDURE RandomString 
@Length int,
@Result varchar(200) OUTPUT
AS
SET @Result = ''
WHILE LEN(@Result) < @Length
BEGIN
SET @Result = @Result + CHAR(CAST(((122 - 97 + 1) * RAND() + 97) AS SmallInt))
END
GO

CREATE PROCEDURE RandomNumber 
@Lower int,
@Upper int,
@Result int OUTPUT
AS
SET @Result = CAST(((@Upper - @Lower + 1) * RAND() + @Lower) AS Int)
GO

--Part 2 - calling the SPs

DECLARE 
@SomeRandomString varchar(50),
@SomeRandomNumber int

EXEC RandomString 30, @Result = @SomeRandomString OUTPUT 
EXEC RandomNumber 1, 25, @Result = @SomeRandomNumber OUTPUT

-- For demonstration purposes only print them
PRINT @SomeRandomString
PRINT @SomeRandomNumber

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating