CODEWITHSUNDEEP
sql-serveruser-defined-functionsstored-functions
Jason M.
Jason M.

Reputation: 11

SQL Server : create function

I am having a little trouble trying to create a function that I can call to generate a 32 character random ID. I have it working in query editor but it is my understanding that to generate this in multiple lines of results accompanied by existing data from tables I have to create a function and call it. Here is my code:

CREATE FUNCTION [dbo].[Func_Gen_OID] (@NewOID varchar(32))
RETURNS VARCHAR(32) AS
BEGIN

DECLARE @Length int = 32
DECLARE @Output varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @Output = ''

SET NOCOUNT ON

SET @counter = 1

WHILE @counter < (@Length + 1)

BEGIN

        SET @RandomNumber = Rand()
        SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))

        SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)

        SET @counter = @counter + 1

        SET @Output = @Output + @CurrentCharacter

RETURN @Output
END

Thanks for any and all help!

Upvotes: 1

Views: 7458

Answers (3)

engin
engin

Reputation: 41

instead of RAND() function, use this:

create view ViewRandomNumbers 
as 
   select rand( ) as Number 
go

Upvotes: 4

MartW
MartW

Reputation: 12538

I'll guess you're seeing this error:

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

You can't use RAND() inside a user-defined function directly (you'd need a view as an intermediary). And in this case, RAND would return the same value for every row of your query anyway as you haven't specified a varying seed (different for each call to the function). If you want to encapsulate the rest of the logic inside the function, you will need to pass it a randomly-generated value from elsewhere, possibly generated via CHECKSUM() and NEWID().

There are some possibilities mentioned in this SQL Server Central thread

Upvotes: 2

Dave Simione
Dave Simione

Reputation: 1441

Two issues I see:

  1. You are returning @Output inside your while loop.
  2. You don't need the input parameter as you aren't using it inside the function

Upvotes: 1

Related Questions