asked on

Function executing too quickly...

I have written the below function to return a random alpha numeric code. This was previously a stored procedure; however, it is no time to convert it to a function. The problem is as a function I can no longer call newid() so I have substituted getdate(). Both fortunately and unfortunately, it executes too quickly and I do not get a random result!

I am not opposed to learning how to do this as a CLR Function (which might open the door to other cool capabilities); however, I don't know where to start.

ALTER FUNCTION dbo.Function2
      (
      @CodeLength as int
      )
RETURNS varchar(255)/* datatype */
AS
BEGIN
declare @NewCode as varchar(255)
Set @NewCode = ''
declare @Character as int

While @CodeLength > 0
BEGIN

/*Generates a random code using 0-9 and A-Z excluding I & O*/
Select @Character = Abs(Cast(getdate() As Binary(16)) % 36)

If @Character < 10 and @Character <> 10
BEGIN
Set @NewCode = @NewCode + Cast (@Character as char(1))
            Set @CodeLength = @CodeLength - 1
END
ELSE
       If @Character > 11 and @Character <> 25 and @Character <> 19
BEGIN
      Set @NewCode = @NewCode + Char(@Character + 54)
            Set @CodeLength = @CodeLength - 1
END

      END
      RETURN @NewCode
      END

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maradam

You can still use NEWID() within a function by implementing the following trick.

CREATE VIEW dbo.vwNewId
as
SELECT NewID() AS NID

then inside a function:

declare @new_id uniqueidentifier
set @new_id = NID from dbo.vwNewId -- and you have newid()

If you don't call newid() directly, the MS check does not work :).

Warning! It may stop work in future versions.

rugby148

ASKER

I should have mentioned that I did try that. The problem is that the unique id needs to change for each character being generated. Since the function can generate any length code, I would need to pass on a varying list of IDs. Right?

rugby148

ASKER

Would creating that view add any significant overhead?

maradam

You can assign @new_id from the view in your loop. You will have different GUID value each time.

maradam

You can try this and compare to your procedure. For me this doesn't add any visible overhead over calling newid() directly. I used this trick several times.

rugby148

ASKER

i must be missing something in what you provided.

i get the following:

incorrect syntax near the keyword 'VIEW'
incorrect syntax near the keyword 'from'

ASKER CERTIFIED SOLUTION

maradam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chapmandew

Are you going to select an answer?