Generating random positive PIN numbers, HELP!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Generating random positive PIN numbers, HELP!

Author

Topic

Dfly
Starting Member

11 Posts

Posted - 2006-10-13 : 09:09:29

Hi,

i want to generate a random pinnumber. Like a password, but then only positive digits. Also i check if pin is already added to table, befor giving out a new one.

i use the following code:

DECLARE @newkey AS INT
SET @newkey = CHECKSUM(RAND()* 10000)

WHILE EXISTS(SELECT ActivationPinCode FROM [Customer].PIN WHERE ActivationPinCode = @newkey)
SET @newkey = CHECKSUM(RAND() * 10000)

SELECT CAST(ROUND(@newkey,0) AS INT )

This generates the numbers just fine, but i don't want negative numbers. This should be fairly simpel, but can anybody help me out?

Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 09:12:20

Use ABS function.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-13 : 09:13:12

[code]SET @newkey = abs(CHECKSUM(RAND()* 10000))[/code]

I am seriously thinking you are running some kind of AutoReply boat

Harsh Athalye
India.
"Nothing is Impossible"

Dfly
Starting Member

11 Posts

Posted - 2006-10-13 : 09:16:50

Thanx Hars and Peso!

Dfly
Starting Member

11 Posts

Posted - 2006-10-13 : 09:20:38

I get the following error on this Function:

ALTER FUNCTION [dbo].[fn_GeneratePinCode]
(
)
RETURNS Int
AS
BEGIN

DECLARE @newkey AS INT
SET @newkey = ABS(CHECKSUM(RAND()* 10000))

WHILE EXISTS(SELECT ActivationPinCode FROM [Customer].PIN WHERE ActivationPinCode = @newkey)
SET @newkey = ABS(CHECKSUM(RAND() * 10000))

SELECT @newkey

RETURN @newkey

END

Msg 443, Level 16, State 1, Procedure fn_GeneratePinCode, Line 15
Invalid use of side-effecting or time-dependent operator in 'rand' within a function.
Msg 443, Level 16, State 1, Procedure fn_GeneratePinCode, Line 18
Invalid use of side-effecting or time-dependent operator in 'rand' within a function.
Msg 444, Level 16, State 3, Procedure fn_GeneratePinCode, Line 21
Select statements included within a function cannot return data to a client.

Anyone nows what's wrong??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-13 : 09:23:32

Remove the following line:

SELECT @newkey

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-10-13 : 09:24:36

I would have something in there that started to "worry" if it had looped "too many times" - 10 maybe?. One day that thing is going to get stuck in that loop, but as the ActivationPinCode table gets more entries the code will start to work harder.

I also don't see it being very atomic - when does the newly allocated entry get inserted into the ActivationPinCode table? Is there a risk of a duplicate being generated within that "window of opportunity"?

I think I would, instead, pre-generate and insert 10,000 unique PIN codes, in random order, (i.e. have an IDENTITY column to force an allocation order, but the numbers would be random if viewed in IDENTITY order) with a USED (bit) column and hand out the first one (i.e. with lowest IDENTITY value) I could find that was not yet marked as USED.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-13 : 09:25:12

RAND() is not allowed inside function since it is non-deterministic function.

Harsh Athalye
India.
"Nothing is Impossible"

Dfly
Starting Member

11 Posts

Posted - 2006-10-13 : 09:39:19

Kristen, you are definitely right about it. Thanx. But maybe have to give out miljons of pincode's, so should i just create a table with miljons of records?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 09:41:22

With only 4 digits (in your original posting), there are only 10,000 possible pincodes.

Peter Larsson
Helsingborg, Sweden

Dfly
Starting Member

11 Posts

Posted - 2006-10-13 : 09:44:39

I know, i was wrong and changed the specs to 9 digits.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 09:45:08

You cannot use RAND in a function. Pass it as an input value to the function. Or make the function a stored procedure.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 09:51:07

quote:
Originally posted by Dfly

I know, i was wrong and changed the specs to 9 digits.

Why do the PIN numbers have to be unique? Are they used as an ID or as a password. If they are used as a password, it is not good security to dis-allow duplicates.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-13 : 09:51:45

This will give you some ideas

http://sqlteam.com/forums/topic.asp?TOPIC_ID=59194

Madhivanan

Failing to plan is Planning to fail

Dfly
Starting Member

11 Posts

Posted - 2006-10-13 : 09:52:24

Ok, i now made a stored procedure and used as a 'fail safe' a unique constraint on customerPincode field. Thank you all.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 03:07:51

quote:
Originally posted by harsh_athalye

I am seriously thinking you are running some kind of AutoReply boat

Busted. I named it Titanic too

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-16 : 03:14:56

quote:
Originally posted by Peso

quote:
Originally posted by harsh_athalye

I am seriously thinking you are running some kind of AutoReply boat
Busted. I named it Titanic too

Peter Larsson
Helsingborg, Sweden

Oh..yeah ?

With such a titanic speed, it was bound to happen

Harsh Athalye
India.
"Nothing is Impossible"

Groker
Starting Member

2 Posts

Posted - 2009-01-30 : 12:09:21

[code]
IF OBJECT_ID('vwDocumentTestQuestion_Rand') IS NOT NULL
DROP VIEW vwDocumentTestQuestion_Rand
GO

CREATE VIEW vwDocumentTestQuestion_Rand AS
SELECT RandomNumber = ABS(RAND()*9999)
GO

IF OBJECT_ID('[fn_GeneratePinCode]') IS NOT NULL
DROP FUNCTION [fn_GeneratePinCode]
GO

CREATE FUNCTION [dbo].[fn_GeneratePinCode]() RETURNS INT AS
BEGIN

DECLARE @newkey INT

SELECT TOP 1 @newkey = RandomNumber FROM vwDocumentTestQuestion_Rand

WHILE EXISTS(SELECT ActivationPinCode FROM [Customer].PIN WHERE ActivationPinCode = @newkey)
SELECT TOP 1 @newkey = RandomNumber FROM vwDocumentTestQuestion_Rand

RETURN @newkey

END
GO

SELECT dbo.fn_GeneratePinCode()
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources