 
Generate a random number within a stored procedure |
SQL Server |
How to create a random number within a SQL Server stored procedure.
The code
-- Create the variables for the random number generation
DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int
-- This will create a random number between 1 and 999
SET @Lower = 1 -- The lowest random number
SET @Upper = 999 -- The highest random number
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)
To adjust the upper and lower limit of the random number simply change the values
of @Lower and @Upper
Using the code within a stored procedure
An example stored procedure inserting a random number along with values from
a web from.
CREATE PROCEDURE spProcedureName
-- The values from the web form
@Textfield1 varchar(20),
@Textfield2 varchar(20)
AS
-- Create the variables for the random number generation
DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int
-- This will create a random number between 1 and 999
SET @Lower = 1 -- The lowest random number
SET @Upper = 999 -- The highest random number
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)
SET NOCOUNT ON
INSERT INTO tblTableName(dbField1, dbField2, RandomNumberField)
VALUES (@Textfield1, @Textfield2, @Random)
SET NOCOUNT OFF
GO |
