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
rugby148Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
You could just alter the function to accept a GUID, and pass the guid into the function when it is called.  That should make it reasonably random.
0
maradamCommented:
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.
0
rugby148Author Commented:
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?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rugby148Author Commented:
Would creating that view add any significant overhead?
0
maradamCommented:
You can assign @new_id from the view in your loop. You will have different GUID value each time.
0
maradamCommented:
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.
0
rugby148Author Commented:
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'
0
maradamCommented:
See atached snippet. A view and a function using it and invocation of that function. Run this in separate query window and then use the view in the same way in your function.
CREATE VIEW dbo.vwNewId
as
SELECT NewID() AS NID
GO
CREATE FUNCTION dbo.fnNewIdInsideFunction()
RETURNS uniqueidentifier
as
begin
  declare @res uniqueidentifier
  select @res = NID from dbo.vwNewId
  return @res
end
GO
select dbo.fnNewIdInsideFunction()

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chapmandewCommented:
Are you going to select an answer?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.