SQLTeam.com | Weblogs | Forums

To use rand() or newid() Inside a Table Function

SQL SERVER 2008 R2

This piece of code generates positive random integers.

declare @range bigint
set @range=100

SELECT name, convert(bigint,@range * RAND(CAST( NEWID() AS varbinary ))) as [Random Number]
FROM sys.objects

I can't use this query within a table function

A sort of work around seems to be here.

http://www.kodyaz.com/articles/how-to-use-sql-newid-in-sql-server-function.aspx

but I am still unable to get what I want.

What I Want
To be able to use this piece of code within the table function.
convert(bigint,@range * RAND(CAST( NEWID() AS varbinary )))
i,e I want to get random number within a certain range.
my piece of code is generating only positive number, whereas I want both negative and
positive value even if I pass a positive value as a parameter

The expected result for @range=100 should be like this sort of
name...., RandomNumber
sysrscols 99
sysrowsets -7
sysclones 84
sysallocunits 77
sysfiles1 -43

why not multiply by -1 randomly !!!

the bigger question is why are you doing this? what are you trying to resolve?

How to get -1 randomly.
Please note that if i don't seed rand() with newid() , then the random -1 multiplier will
be repeated in the entire result.

How yosiasz
<<the bigger question is why are you doing this? what are you trying to resolve?>>

  1. I often write parameterized view (table function)
    and use this table in various store procedures.

  2. I have many procedures where I have used the above command
    convert(bigint,@range * RAND(CAST( NEWID() AS varbinary ))).

Today, after copy/paste from one such procedure and creating a table function
I got error "Invalid use of side-effect operator".

it took hours to me why this was working on one of my object and not in another
before I realized one is a "Procedure" and the other is a "Function"

DECLARE @range bigint
SET @range=100

SELECT TOP (500) name, CASE WHEN Random_Number = @range THEN -@range ELSE Random_Number - @range END AS Random_Number
FROM sys.objects
CROSS APPLY dbo.GenNewId AS GNI
CROSS APPLY (
    SELECT CONVERT(bigint, @range * 2 * RAND(CAST(GNI.GenNewId AS varbinary(16)))) AS Random_Number
) AS ca1

/*

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
--GO
CREATE OR ALTER VIEW dbo.GenNewId
WITH SCHEMABINDING
AS
SELECT TOP (1) NEWID() AS GenNewId
--GO

*/
1 Like

I was trying to generate random intime on the basis of office start time, within 15 minutes
boundry, something like this
declare @t table (code int, trans_date datetime, intime varchar(8), office_start_timein varchar(8))
insert @t (code,trans_date,office_start_timein)
select 123, '2020-09-01', '09:00:00' union all
select 123, '2020-09-02', '09:00:00' union all
select 123, '2020-09-03', '09:00:00'

declare @range int
set @range = 900

select , timein= dateadd(s,(convert(bigint,(960*60) + @range * RAND(CAST( NEWID() AS varbinary )))),trans_date)
from @t

Hi ScottPletcher
Just like that!!!
My lifetime desire is achieved.

Thanks and very best regards.

In store procedure
to get negative and positive both
i was using a tally table with negative and positive number
and then getting with
select top 1 from tallytable order by newid()

Your code is without tally table!!!!
and will work in function as well

You're welcome. Thanks for the positive feedback, it is very much appreciated.

Hi ScottPletcher

I thought it would work in function but unfortunately not.

CREATE VIEW dbo.GenNewId
WITH SCHEMABINDING
AS
SELECT TOP (1) NEWID() AS GenNewId
go

create function f_abc()
returns @t table (name varchar(100),random_number int)
as
begin

DECLARE @range bigint
SET @range=100
insert @t (name,random_number)
SELECT TOP (500) name, CASE WHEN Random_Number = @range THEN -@range ELSE Random_Number - @range END AS Random_Number
FROM sys.objects
CROSS APPLY dbo.GenNewId AS GNI
CROSS APPLY (
SELECT CONVERT(bigint, @range * 2 * RAND(CAST(GNI.GenNewId AS varbinary(16)))) AS Random_Number
) AS ca1
RETURN
end

Invalid use of a side-effecting operator 'rand' within a function.

I am sure you will find a way.

Hi all

Manage to create table function to generate random values

CREATE FUNCTION dbo.itvf_Rand ( )
RETURNS TABLE
AS RETURN
( SELECT CHECKSUM(PWDENCRYPT(N'aa')) / 2147483647.0 AS R)
GO

create function f_rand_in_function_possible()
returns @t2 table (code varchar(100),office_start_time varchar(8),intime varchar(8))
as
begin
declare @t table (code int, trans_date datetime, intime varchar(8), office_start_time varchar(8))
insert @t (code,trans_date,office_start_time)
select 123, '2020-09-01', '09:00:00' union all
select 123, '2020-09-02', '09:00:00' union all
select 123, '2020-09-03', '09:00:00'

declare @range int
set @range = 900

insert @t2 (code,office_start_time,intime)
select code, office_start_time,timein= convert(varchar,dateadd(s,32400+(select convert(int,r*@range) from dbo.itvf_rand()),trans_date),108)
from @t
return
end

go
select *
from dbo.f_rand_in_function_possible()

code office_start_time	intime (random)
123	09:00:00	                09:02:31
123	09:00:00	                08:55:24
123	09:00:00	                08:47:01

Hi ScottPletcher

This is working

CREATE FUNCTION dbo.itvf_Rand ( )
RETURNS TABLE
AS RETURN
( SELECT CHECKSUM(PWDENCRYPT(N'aa')) / 2147483647.0 AS R)
GO

create function f_rand_in_function_possible()
returns @t2 table (name varchar(500),random_number int)
as
begin

declare @range int
set @range = 900

insert @t2 (name,random_number)
select top 500 name, (select convert(int,r*@range) from dbo.itvf_rand())
from sys.objects
return
end

go
select *
from dbo.f_rand_in_function_possible()

Sorry about my mix-up there. Please try this instead:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER VIEW dbo.GenRandValue
WITH SCHEMABINDING
AS
SELECT TOP (1) RAND(CAST(NEWID() AS varbinary(16))) AS RandValue
GO

CREATE FUNCTION dbo.func1
(
    @range bigint
)
RETURNS TABLE
AS
RETURN (
    SELECT TOP (500) name, CASE WHEN Random_Number = @range THEN -@range ELSE Random_Number - @range END AS Random_Number
    FROM sys.objects
    CROSS APPLY dbo.GenRandValue AS GRV
    CROSS APPLY (
        SELECT CONVERT(bigint, @range * 2 * GRV.RandValue) AS Random_Number
    ) AS ca1

Scottpletcher

It was really blessing for me.
I found beautiful beautiful
CHECKSUM(PWDENCRYPT(N'aa'))

Thanks again.

BTW: I think I know you , since 10-20 years back, when I was paid member of
Experts-exchange.com, and there I learned a lot from you and Angel