Can I strongly suggest the other method which I mentioned before. Your system doesn't scale - the more codes you generate, the greater the chance of regenerating an existing code, and the more time your sproc spends in its loop. Additionally, you need to use a sufficiently high isolation level when running this sproc so that some other thead running the same sproc doesn't generate the same code. Create a table of all possible values. Use another field to indicate whether the code has been used or not. The following query: SELECT TOP 1 Code FROM MasterTableOfCodes WHERE CodeUsed = 0 ORDER BY NewID() Will select just a single, random record from that table. There is no need for loops. You will want to use at least a "repeatable read" isolation level for this type of transaction (the default is read committed, but I don't think that's sufficient for what you want to achieve). Cheers Ken : -----Original Message----- : From: thelist-bounces at lists.evolt.org [mailto:thelist- : bounces at lists.evolt.org] On Behalf Of Brian Cummiskey : Sent: Saturday, 18 March 2006 5:11 AM : To: thelist at lists.evolt.org : Subject: Re: [thelist] math help... : : Hi Ken, : : Thanks for the response. : : : Ken Schaefer wrote: : > What sort of transaction isolation are you using? In a multi-threaded : > environment, what happens when you have two threads with the same value : that : > both pass the first part of the test, but then baulk at the second? : : It's using a modulus process. I didn't write this process, and it is in : use on a lot of our jobs here at the office. This isn't running on a : transaction. I think the best way would be to post the whole SP. See : below. : : > Secondly, do you really think this is a good idea? I don't. It really : won't : > scale as you get close to the maximum number of possible values as your : > generator routine will mostly generate already used values. : : I don't think this will be a problem. I doubt we will ever reach the : number that was posted yesterday. If it is, I can simply add another : pre-fix character to being with, such as BST2. : : > But do you really need random IDs? It seems you just need IDs that : haven't : > been used before. : : They need to be random, as they are given to sales reps as their : commission ID. If they learn that its just the next number in order, : there could be some cheating. : : Could I suggest you use GUIDs instead? You can either set a : > field to a GUID type, or set it to varchar/char and use NewID() to : generate : > the value and insert it. : : I just read this: http://www.webopedia.com/TERM/G/GUID.html quickly, and : i think it's overkill. : Our phone agents read back these numbers to the sales rep's. So, it : needs to be tiny, normalized (the BST prefix), and easy to write down : over the phone. : : : : : : : SET QUOTED_IDENTIFIER ON : GO : SET ANSI_NULLS OFF : GO : : ALTER PROCEDURE get_Confirmation_Code_TABLE : @confirmation_code varchar(8) output, : @rowid int output : AS : declare @counter int, : @sum int, : @charNum int : repeat: : SET @counter = 1 : SET @sum=0 : SET @confirmation_code='BST' : while @counter<6 : begin : SET @charNum=convert(int,(RAND()*24)) : IF @charNum<10 set : @confirmation_code=@confirmation_code+CHAR(@charNum+48) : IF @charNum=10 set : @confirmation_code=@confirmation_code+'A' : IF @charNum=11 set : @confirmation_code=@confirmation_code+'C' : IF @charNum=12 set : @confirmation_code=@confirmation_code+'E' : IF @charNum=13 set : @confirmation_code=@confirmation_code+'F' : IF @charNum=14 set : @confirmation_code=@confirmation_code+'G' : IF @charNum=15 set : @confirmation_code=@confirmation_code+'H' : IF @charNum=16 set : @confirmation_code=@confirmation_code+'J' : IF @charNum=17 set : @confirmation_code=@confirmation_code+'K' : IF @charNum=18 set : @confirmation_code=@confirmation_code+'M' : IF @charNum=19 set : @confirmation_code=@confirmation_code+'R' : IF @charNum=20 set : @confirmation_code=@confirmation_code+'T' : IF @charNum=21 set : @confirmation_code=@confirmation_code+'U' : IF @charNum=22 set : @confirmation_code=@confirmation_code+'W' : IF @charNum=23 set : @confirmation_code=@confirmation_code+'X' : IF @charNum=24 set : @confirmation_code=@confirmation_code+'Y' : set @sum=@sum+ at charNum : set @COUNTER=@COUNTER+1 : end : -----wape off some bad word : IF @confirmation_code LIKE '%BADWORDS%' goto repeat : : -----confirm modulus of 25 : if ((@sum/25.0)<>floor(@sum/25)) goto repeat : : if exists(select confirmation_code from TABLE where : confirmation_code=@confirmation_code ) : ---the confirmation code has been used : begin : goto repeat : end : else : ---get the cconfirmation code successfully : begin : insert TABLE (confirmation_code) values : (@confirmation_code) : end : if @@error=0 : begin : set @rowid = @@identity : end : : GO : SET QUOTED_IDENTIFIER OFF : GO : SET ANSI_NULLS ON : GO : : -- : : * * Please support the community that supports you. * * : http://evolt.org/help_support_evolt/ : : For unsubscribe and other options, including the Tip Harvester : and archives of thelist go to: http://lists.evolt.org : Workers of the Web, evolt !