rand function problem

joxa83 · May 16th, 2006, 05:01 AM

I need to call rand functino within another function but server raises an error.For example I can't create followng function

create function dbo.test()
returns float
as
begin
declare @i float
select @i = rand()
return @i
end

Jeff Mason · May 16th, 2006, 05:20 AM

You cannot call a nondeterministic function from within a user defined function. Examples of nondeterministic functions are GETDATE, NEWID, and, alas, RAND.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

joxa83 · May 16th, 2006, 06:13 AM

thnx. I did not know that.

joxa83 · May 16th, 2006, 07:34 AM

i came uo with this alternative:

create view vw_test
as
select 1 value1, rand() value2

alter function dbo.test()
returns float
as
begin
declare @i float
set @i = (select value2 from vw_test)
return @i
end

select dbo.test()

Jeff Mason · May 16th, 2006, 07:10 PM

Just out of curiosity, why do you need a function to return a random number?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

joxa83 · May 19th, 2006, 06:20 AM

I need something that will return random number in some interval
like

select myradnom(i1.first,i2.second) from mytable

I need this for creating some examples.

Jeff Mason · May 19th, 2006, 07:18 AM

OK. The reason I asked was if you wanted to return a set of rows selected randomly from a table, you can use the NEWID() function, as for example if you needed to select 5 random rows:

Code:

      SELECT TOP 5 *
        FROM mytable
    ORDER BY NEWID();

I've used this trick for generating audit data...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

joxa83 · May 19th, 2006, 08:47 AM

No but You helped me because I was thinking about that problem too.I need to implement it in some web aplication.Thnx againg :)

David_the_DBA · May 19th, 2006, 11:07 AM

In 2005 there is the TABLESAMPLE keyword that can be used the in FROM clause, although you can't use it inside of a function or a view.

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Jeff Mason · May 19th, 2006, 11:18 AM

Quote:

quote:Originally posted by David_the_DBA
In 2005 there is the TABLESAMPLE keyword that can be used the in FROM clause ...

Yeah, but as I understand the way it works, TABLESAMPLE either includes or excludes entire pages in the resultset. Depending on the size of your table rows, and the distribution of values in the clustered index/heap, this may be far from a random sampling of the data.

Even using the NEWID() function, I've had auditors question the ramdomness of a GUID...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com