[thelist] math help...
Brian Cummiskey
Brian at hondaswap.com
Fri Mar 17 12:11:23 CST 2006
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
More information about the thelist
mailing list