[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Function executing too quickly...

Posted on 2008-01-31
9
Medium Priority
?
298 Views
Last Modified: 2008-03-31
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
0
Comment
Question by:rugby148
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 20791936
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
 
LVL 7

Expert Comment

by:maradam
ID: 20791961
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
 

Author Comment

by:rugby148
ID: 20791963
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:rugby148
ID: 20792015
Would creating that view add any significant overhead?
0
 
LVL 7

Expert Comment

by:maradam
ID: 20792035
You can assign @new_id from the view in your loop. You will have different GUID value each time.
0
 
LVL 7

Expert Comment

by:maradam
ID: 20792063
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
 

Author Comment

by:rugby148
ID: 20792165
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
 
LVL 7

Accepted Solution

by:
maradam earned 1200 total points
ID: 20794543
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21010990
Are you going to select an answer?
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

613 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question