The RAND function does not behave as you would expect in SQL Server. If it is included in a select statement then it will generate a single number and populate every row with that value.
Here is a bit of a complex way of getting around this problem. Wrapping the RAND function in a view and calling via a UDF.
Modify as required but remember be careful of resource usage calling functions in select statement
CREATE VIEW vw_RandNumber_tempDEVOnly
AS
--gerard 28/10 this is a temporary setup to generate random data to allow me to develop a report in a system where we haven't put the required grouping data
SELECT RAND() as RandNumber
ALTER FUNCTION fRandNumber_tempDEVOnly(@Min int, @Max int)
RETURNS float
AS
--gerard 28/10 this is a temporary setup to generate random data to
allow me to develop a report in a system where we haven't put the
required grouping data
BEGIN
RETURN ROUND(@Min + (select RandNumber from vw_RandNumber_tempDEVOnly) * (@Max-@Min),0)
END
--call like this
SELECT dbo.fRandNumber_tempDEVOnly(1,3)
