SQL script to generate random number and decimal as per row basis
February 4th, 2009 — ¥ong¥s
Online project management, task software, and free collaboration workspace: AJAXWorkspace
AJAXWorkspace is a free online workspace that offers task tracking software, team collaboration, online calendar, document sharing, and file management for project management and teamwork. |
This is nothing new on how to generate random number as per row basis. I am using NewID() method and complied into SQL function. Just to keep as my own record. Here are the 2 functions which return integer and decimal value.
Function:
CREATE FUNCTION dbo.udf_GetRandomNumber(
@GUID AS UNIQUEIDENTIFIER,
@MinValue AS INT = 0,
@MaxValue AS INT = 1000
)
RETURNS INT AS
BEGIN
�
RETURN ABS(CAST(CAST(@GUID AS VARBINARY) AS INT) % (@MaxValue - @MinValue + 1)) + @MinValue
END
Usage:
SELECT dbo.GetRandomNumber(NewID(),100,500) FROM TableA
– Return random number from 100 to 500 as per row basis
Function:
CREATE FUNCTION dbo.udf_GetRandomDecimal(
@GUID AS UNIQUEIDENTIFIER,
@MinValue AS INT = 0,
@MaxValue AS INT = 1000,
@GUIDDecimal AS UNIQUEIDENTIFIER = NULL
)
RETURNS DECIMAL (18,2) AS
BEGIN
DECLARE @dResult AS DECIMAL (18,2)
IF @GUIDDecimal IS NOT NULL
SET @dResult = ABS(CAST(CAST(@GUID AS VARBINARY) AS INT) % (@MaxValue-@MinValue + 1)) + @MinValue + ABS(CAST(CAST(@GUIDDecimal AS VARBINARY) AS INT) % 100)/100.0
ELSE
SET @dResult = ABS(CAST(CAST(@GUID AS VARBINARY) AS INT) % (@MaxValue-@MinValue + 1)) + @MinValue
RETURN @dResult
END
Usage:
SELECT dbo.GetRandomDecimal(NewID(),100,500,NewID()) FROM TableA
– Return random decimal number from 100.00 to 500.99 as per row basis