Stored Procedures

Question

Post reply

Stored Procedures

adeomoboya

Old Hand

Points: 341
More actions
August 3, 2009 at 10:17 pm

#224337

hi, i would needa sample script of a t-sql stored procedures that can generate a 13-15 random numbers

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736211 More actions · Answer 1

http://www.sqlservercentral.com/scripts/Miscellaneous/31793/

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 2

I never had good luck with the RAND() function. I'd suggest using NEWID() for generating random data.

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/04/04/populating-sample-data.aspx

For example:SELECT ABS(CHECKSUM(NEWID())) % 10000 as IntegerVal

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

rand() solution is not that bad either...

DECLARE @counter smallint

SET @counter = 1

WHILE @counter < 5

BEGIN

SELECT (RAND(convert(int,DATEPART(ms, GETDATE())+@counter))*1000000000000000) Random_Number

SET NOCOUNT ON

SET @counter = @counter + 1

SET NOCOUNT OFF

END

GO

Random_Number

----------------------

726635073093664

(1 row(s) affected)

Random_Number

----------------------

726653706064922

(1 row(s) affected)

Random_Number

----------------------

726672339036180

(1 row(s) affected)

Random_Number

----------------------

726690972007438

(1 row(s) affected)

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Ummmmm.... see if you like the random numbers this gives you... includes some with leading zero's but will always have 13 to 15 digits...

SELECT TOP 100000

RIGHT(

CAST(CAST(NEWID() AS VARBINARY) AS BIGINT),

ABS(CHECKSUM(NEWID()))%3+13)

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 5

That's beautious, Jeff... I liked it so much I decided to make a stored procedure out of it, that's customizable on how many numbers you get, along with the span of the number of digits (max of 18 total). Here's the code:

CREATE PROCEDURE dbo.RANDOM_NUMBERS(@HOW_MANY AS BIGINT, @MINIMUM_DIGITS AS tinyint, @ADDITIONAL_DIGITS AS tinyint)

AS

BEGIN

-- ***************************************************************************************************************** --

-- * AUTHOR: Steve Munson * --

-- * CREATED: 08/06/2009 @ 09:00 AM * --

-- * * --

-- * This stored procedure uses NEWID() as a means to generate random numbers of at least @MINIMUM_DIGITS digits, * --

-- * with up to @ADDITIONAL_DIGITS more digits, and return @HOW_MANY of them. Initial idea for this method stems * --

-- * from code posted by Jeff Moden on SQL Server Central. Thanks, Jeff! * --

-- ***************************************************************************************************************** --

IF @HOW_MANY < 1 OR @MINIMUM_DIGITS < 1 OR @ADDITIONAL_DIGITS 18

BEGIN

DECLARE @ERROR_MSG AS nvarchar(200)

SET @ERROR_MSG = 'Incorrect parameter specified. You must specify a positive integer for all parameters except' +

CHAR(13) + CHAR(10) + '@ADDITIONAL_DIGITS, which must be either 0 or a positive integer. 18 digits maximum.'

RAISERROR (@ERROR_MSG,4,1)

END

ELSE

BEGIN

SELECT TOP (@HOW_MANY)

CAST(RIGHT(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT),

ABS(CHECKSUM(NEWID()))%(@ADDITIONAL_DIGITS + 1)+@MINIMUM_DIGITS) AS bigint) AS RANDOM_NUMBER

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

END

GO

Steve

(aka smunson)

:-):-):-)

Jeff Moden (8/4/2009)
Ummmmm.... see if you like the random numbers this gives you... includes some with leading zero's but will always have 13 to 15 digits...
SELECT TOP 100000
RIGHT(
CAST(CAST(NEWID() AS VARBINARY) AS BIGINT),
ABS(CHECKSUM(NEWID()))%3+13)
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.All_Columns ac2

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)