[thelist] math help...

[Brian Cummiskey]

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