Problem
When I use the SQL Server RAND() T-SQL function as a column in a SELECT statement, the same value is returned for every row as shown below. How can I get differing random values on each row?
function.JPG)
Solution
In this tip, we will show how to create your own user defined function in T-SQL that will return a random number for every row of your query. For this function to produce the desired results, we have to force the database engine to call the built-in RAND function once for each row. This will be accomplished by creating a view that contains a call to the RAND function, and then having our user defined function select from the view.
Let’s begin by creating our view using the T-SQL CREATE VIEW statement below.
USE MSSQLTips GO CREATE VIEW dbo.myRandomNumberView AS SELECT randomNumber = RAND(); GO
Next, we will define our function with the T-SQL below.
CREATE FUNCTION dbo.myRandomNumberFunction()
RETURNS DECIMAL(12,11)
AS
BEGIN
RETURN (SELECT randomNumber FROM dbo.myRandomNumberView);
END
GO
Now, we are ready to test our random number function.
Notice in the image above that each row in the result set has a differing value in the RandomNumber2 column.
The data type returned by the function can be changed to meet your needs. For example, if you need a random amount less than 100 with 2 decimal places you could create the function as shown in the T-SQL below.
CREATE FUNCTION dbo.myRandomNumberFunction()
RETURNS DECIMAL(5,2)
AS
BEGIN
RETURN (SELECT randomNumber*100 FROM dbo.myRandomNumberView);
END
GO
Notice how when we execute myRandomNumberFunction, the RandomNumber2 column contains amounts less than 100 with 2 decimal places.
Next Steps
Check out these other tips and tutorials on T-SQL and the RAND() function on MSSQLTips.com.
