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
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
*/
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
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
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.
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()
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