Monday, February 21, 2011

Generating random numbers in a specific range using MS SQL Server

In the post Generating Random numbers in a SELECT statement using MS SQL Server we saw how random numbers can be generated using Microsoft MS SQL Server. We will use the dbo.random function from this blog in order to create a new function called dbo.random_range.
This function will generate a random number for a specific range. For example: we might need to generate a random number between 100 and 1000.
The dbo.random_range function will return a random number between @start and @end parameters. The dbo.random function generates a random number between 0 and 1. In order to create a random number for a given range we will use a small mathematical calculation which is pretty easy and straightforward. Therefore we will simply understand it by looking at the dbo.random_range function:

CREATE FUNCTION [dbo].[random_range](@start int, @end int)
  return @start + dbo.random() * (@end - @start + 1)

No comments:

Post a Comment