Random Integers

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.

http://www.swirlhost.com/Blogs/tabid/153/EntryID/5/Default.aspx

Since I needed to generate integers between two values, here is the T-SQL  script I wrote.

Create View v_rnd
as
select RAND(datepart(MICROSECOND,getdate())) as RndNo
go


CREATE FUNCTION dbo.RandomKey_Get (@minval int, @maxval int)
RETURNS int

AS

BEGIN
declare @RndKeyVal int
declare @RndBase as float

set  @RndBase = (select top 1 RndNo from  v_rnd)

set @RndKeyVal=@minval + floor(@maxval * @RndBase);

return @RndKeyVal;
END
GO

I hope that this might be of help to some of you.

Enjoy.

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.