SQL Server : create function

Question

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!

Just asking but why not just use a unique identifier with a default of newid(). It's not alpha numerical but gauranteed to be unique and is very random. — Namphibian
– Namphibian, Commented May 10, 2012 at 16:12

engin · Accepted Answer · 2012-10-23 09:43:30Z

4

instead of RAND() function, use this:

create view ViewRandomNumbers 
as 
   select rand( ) as Number 
go

answered Oct 23, 2012 at 9:43

engin

412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thelem Over a year ago

Accurate, but there is an older answer explaining why this is necessary.

MartW · Accepted Answer · 2012-05-10 15:47:18Z

2

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

answered May 10, 2012 at 15:47

MartW

12.6k3 gold badges46 silver badges68 bronze badges

1 Comment

Jason M. Over a year ago

Thanks guys I was going about this the entire wrong way. I combined the use of a view with the NEWID() to get done what I needed creating the new view as: SELECT REPLACE(CONVERT(varchar(36), NEWID()), '-', '') AS OID

Dave Simione · Accepted Answer · 2012-05-10 15:40:12Z

1

Two issues I see:

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

answered May 10, 2012 at 15:40

Dave Simione

1,4412 gold badges22 silver badges32 bronze badges

Collectives™ on Stack Overflow

SQL Server : create function

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related