Originally posted on February 16 2013
Just to confuse you a little more, here is a technical post. Once in a while, I dismiss the business attire to do something practical (I am paid for this, too).
I am building data for a demo/test database for something I am writing and I had to generate random key numbers in SQL server.
I tried to build a UDF but I was remembered, in a quite rude way, that you can’t use some non-deterministic functions (in this case RAND() ) in User Defined Functions because of side effects.
Msg 443, Level 16, State 1, Procedure RandomKey_Get, Line 13
Invalid use of a side-effecting operator 'rand' within a function.
Since I do not like being denied doing what I need to do for such “marginalia” (details of little importance, in latin), I googled a while and I found an elegant solution that works well in sql 2012 too.
Since I needed to generate integers between two values, here is the T-SQL script I wrote.
Create View v_rnd
select RAND(datepart(MICROSECOND,getdate())) as RndNo
CREATE FUNCTION dbo.RandomKey_Get (@minval int, @maxval int)
declare @RndKeyVal int
declare @RndBase as float
set @RndBase = (select top 1 RndNo from v_rnd)
set @RndKeyVal=@minval + floor(@maxval * @RndBase);
I hope that this might be of help to some of you.
PS: you, hey you, SQL Server geek who landed here in search of solutions, just you. Have a look at the previous posts. They are not canned solutions ready to use, but I hope that they could contribute to open your mind to what Business Intelligence really is.