simply create the function on the database and access it like this:
select dbo.fx_getrandomnumber(500, 300, rand())
if @maxvalue is null and @minvalue is null then a random number between 0 and a hundred is returned.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: CHRIS MORTON -- Create date: 19-11-08 -- Description: GENERATES A RANDOM INTEGER BETWEEN A CERTAIN RANGE. DEFAULT RANDOM NUMBER BETWEEN 0 - 100 -- ============================================= ALTER FUNCTION [dbo].[FX_GetRandomNumber] ( @MaxValue BIGINT, @MinValue BIGINT, @RandomNumber FLOAT ) RETURNS BIGINT AS BEGIN DECLARE @RandomInteger BIGINT IF @MaxValue IS NULL BEGIN SET @MaxValue = 100 END IF @MinValue IS NULL BEGIN SET @MinValue = 0 END SELECT @RandomInteger = ( ( @MaxValue + 1 ) - @MinValue ) * @RandomNumber + @MinValue RETURN @RandomInteger END
Building random numbers can be challenging for people working in T-SQL. New author Chris Nowicki brings a solution he used to get a random number assigned to each row of a result set.
2009-06-09
5,075 reads
this is a MUCH simpler method of random randomize randomise rand data from a table
2008-11-19
283 reads
Sometimes you just need to populate a CHAR/VARCHAR column for testing with a random selection of string literals at table CREATE time. Here is a quick and easy script.
2012-06-14 (first published: 2008-01-16)
1,146 reads
Image a situation when you use the SQL Server RAND() T-SQL function as a column in a SELECT statement, and the same value is returned for every row as shown below. In this tip, Dallas Snider explains how you can get differing random values on each row.
2015-04-02
10,277 reads
Learn how you can easily generate large amounts of test data efficiently in this new series from Jeff Moden. Part 1 covers integers and floating point values.
2012-03-26
10,858 reads
