TSQL RAND() Issue

random, sql, t-sql, user-defined-functions

Solution

You can do this as a two-step process.

First, create a view to generate your random number:

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

Second, create your `UDF` to pull from the view:

CREATE FUNCTION dbo.udfTest
(
)
RETURNS nvarchar(50)
AS
BEGIN

    DECLARE @result  nvarchar(50)
    DECLARE @counter smallint, @ci smallint, @cu smallint, @dc smallint

    SET @ci=(SELECT RandNumber FROM vRandNumber)*100
    SET @cu=(SELECT RandNumber FROM vRandNumber)*100
    SET @dc=(SELECT RandNumber FROM vRandNumber)*100

    set @result = CAST(@ci AS varchar(2)) +'-'+CAST(@cu AS varchar(2))+'-'+CAST(@dc AS varchar(2))

RETURN @result
END

This will return your value that you just requested. Then when you want your value your just use and you will get your random answer:

SELECT dbo.udfTest()

OR

INSERT INTO yourTable 
(
    randNumber
)
SELECT dbo.udfTest()

I just tested this in sql server 2005 and it worked.

Problem

I'm trying to generate a string formatted like: 99-88-77 where the three 2digit numbers are randomly generated. My TSQL that works: ``` declare @result nvarchar(50) DECLARE @counter smallint, @ci smallint, @cu smallint, @dc smallint SET @ci=RAND()*100 SET @cu=RAND()*100 SET @dc=RAND()*100 --SET @counter = @counter + 1 set @result = CAST(@ci AS varchar(2)) +'-'+CAST(@cu AS varchar(2))+'-'+CAST(@dc AS varchar(2)) print @result ``` Produces (this time): 16-37-30 I need to get this string for every record inserted into a table. Now I would like to wrap this into a function, but apparently I can't use RAND() in a UDF. How can I wrap this to call when using an insert statement?

Original source