generate random number - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

generate random number

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 12:38:28

how can i generate a 7 digit random number?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-07 : 13:29:57

SELECT CAST(RAND()*10000000 AS INT)

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-07 : 13:31:10

SELECT cast(round((8999998 * Rand() + 1000000), 0) as int)

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 13:36:07

thanks...

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 13:40:36

one more thing if you can help (thanks)

i need to add to this sp to check the customer table and if any records exist with p= the new generated p then it should regenerate -- how can i do this?
(it needs to be a random 7 digit unique number)

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-07 : 13:57:20

Why only 7 digits?

Have a look at

select newid()

Or just use IDENTITY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-07 : 13:58:10

DECLARE @NewNumber int
WHILE @NewNumber IS NULL
BEGIN
SET @NewNumber = cast(round((8999998 * Rand() + 1000000), 0) as int)
IF EXISTS (SELECT * FROM Customer WHERE p = @NewNumber)
SET @NewNumber = NULL --Not unique
ELSE
--Use @NewNumber, it is unique
END
--Can use
--SELECT @NewNumber
--here if you want to return the number that was just inserted

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 14:09:31

ok so my revised code is

ALTER PROCEDURE [dbo].[spnewp]
-- Add the parameters for the stored procedure here
(@p int output)
AS
BEGIN

SET NOCOUNT ON;

WHILE @p IS NULL
BEGIN
SET @p = cast(round((8999998 * Rand() + 1000000), 0) as int)
IF EXISTS (SELECT * FROM customers WHERE p = @p)
SET @p = NULL --Not unique
ELSE
--Use @NewNumber, it is unique
END

select @p

i'm getting error

Msg 156, Level 15, State 1, Procedure spnewp, Line 24
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Procedure spnewp, Line 26
Incorrect syntax near 'OFF'.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-07 : 14:39:11

You can use the F_RANDOM_INTEGER function on the link below.

Random Integer, Sample, and Datetime Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 14:45:18

but what's wrong in my syntax above?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-07 : 14:55:23

quote:
Originally posted by esthera

but what's wrong in my syntax above?

It's incorrect.

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 15:00:25

what is wrong with it? the selecting of random number worked but within the stored procedure there is an error

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-07 : 16:02:33

You're missing the END keyword at the end of the procedure, you have a BEGIN at the beginning, but no matching END.

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-07 : 16:43:35

ooooooooooooooooooooooooooooooooooooooooooooooooook

Let's take a step back

What's the reason behind all this random key generation?

Hell it's worse than surrogates....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-07 : 17:38:18

You want the requirements to also make sense?

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-08 : 00:41:00

can you please explain me how to do this?

this works SELECT cast(round((8999998 * Rand() + 1000000), 0) as int)

why doesn't it work in the sp?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 00:44:45

"why doesn't it work in the sp?"
How can we help you if you did not post the full SP with the error message.

You have this error

quote:
Msg 156, Level 15, State 1, Procedure spnewp, Line 24
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Procedure spnewp, Line 26
Incorrect syntax near 'OFF'.

But there isn't any key word OFF in the partial SP that you posted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources