Translate this in a stored procedure

Question

Translate this in a stored procedure

Corobori

Ten Centuries

Points: 1316
More actions
January 11, 2006 at 5:22 pm

#93161

I have the code shown below in a VB code which I need to run in a Stored Procedure.
This code needs to create a 6 digits code. I am looping to build the code either with a letter or number.
Is it possible to "translate" it and use this in a SP ?
Dim s(25) As String
s(1) = "A"
s(2) = "B"
s(3) = "C"
s(4) = "D"
s(5) = "E"
s(6) = "F"
s(7) = "G"
s(8) = "H"
s(9) = "J"
s(10) = "K"
s(11) = "L"
s(12) = "M"
s(13) = "N"
s(14) = "P"
s(15) = "Q"
s(16) = "R"
s(17) = "S"
s(18) = "T"
s(19) = "U"
s(20) = "V"
s(21) = "W"
s(22) = "X"
s(23) = "Y"
s(24) = "Z"
For j = 1 To 6 ' Build a 6 digit code
Randomize()
MyValue = Int((2 * Rnd()) + 1) ' Letter or number ?
If MyValue = 1 Then ' Number
Randomize()
MyValue = Int((8 * Rnd()) + 1)
sNumero = sNumero & MyValue
Else ' Letter
Randomize()
MyValue = Int((24 * Rnd()) + 1)
sNumero = sNumero & s(MyValue)
End If
Next

Jean-Luc
www.corobori.com

Viewing 10 posts - 1 through 9 (of 9 total)

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

If you goal is to generate a random six character string, then this seems to work. The main problem in generating random numbers in SQL Server is providing an appropriate seed value for the RAND() function.

DECLARE @MyCode varchar(6)
DECLARE @Rnd int

SET @MyCode = ''
SET @Seed = DATEPART(ms, GETDATE()) * DATEPART(ms, GETDATE())

WHILE LEN(@MyCode) < 6
BEGIN

    SET @Seed = @Seed + (@Seed * RAND(@Seed))
    SET @MyCode = @MyCode + CHAR((RAND(@Seed) * 26.0) + 65)

END

PRINT @MyCode

--------------------
Colt 45 - the original point and click interface

Corobori Ten Centuries Points: 1316 More actions · Answer 2

Thanks, you just missed the Declare @Seed int but otherwise it's fine

Jean-Luc
www.corobori.com

philcart SSC-Forever Points: 47794 More actions · Answer 3

Ahhh ... renamed all but the DECLARE ... DOH!!!

Also, dredged this up which works as well.

DECLARE @MyCode varchar(6)
DECLARE @Min int
DECLARE @Max int;

SET @MyCode = ''
SET @Min = 65
SET @Max = 90

WHILE LEN(@MyCode) < 6
BEGIN
    SET @MyCode = @MyCode +  CHAR(Round(((@Max - @Min -1) * Rand() + @Min), 0))
END

PRINT @MyCode

And here is a variation that uses SUBSTRING to select from a range of characters you specify,

DECLARE @MyCode varchar(6)
DECLARE @Valid varchar(100)
DECLARE @Max int

SET @MyCode = ''
SET @Valid = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
SET @Valid = @Valid + '0123456789-=[];,./~!@#$%^&*()_+|{}:<>?'
SET @Max = LEN(@Valid)

WHILE LEN(@MyCode) < 6
BEGIN
    SET @MyCode = @MyCode +  SUBSTRING(@Valid, CAST((((@Max * Rand()) + 1)) as int), 1)
END

PRINT @MyCode

Of course with these samples you could remove the WHILE loop and copy the CHAR/SUBSTRING for as many characters as you need.

--------------------
Colt 45 - the original point and click interface

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

Here's a set-based method...

SELECT CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)

+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)

+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65) AS MyCode

Also, I may be incorrect, but I don't believe any of these methods posted, so far, guarantee uniqueness although approximately 6^36th is a heck of a lot of combinations.

--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)

philcart SSC-Forever Points: 47794 More actions · Answer 5

Jeff, thanks for illustrating what I said

"Of course with these samples you could remove the WHILE loop and copy the CHAR/SUBSTRING for as many characters as you need."

--------------------
Colt 45 - the original point and click interface

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 6

You say 'em, I'll make 'em. What a team! The neat thing here is that using NEWID as the seed for RAND allows true set based ops... using RAND with any other seed might allow for adjacent duplication... try the following...

SELECT TOP 100

CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)

+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65)

+ CHAR(RAND(CAST (NEWID() AS VARBINARY))*26+65) AS MyCode

FROM dbo.SYSCOMMENTS

'course... using a Tally table as a row control would be even better

--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)

paulhumphris Hall of Fame Points: 3496 More actions · Answer 7

Why not just use the newid() function to generate the random string.

declare @MyCode varchar(6)

while @MyCode is null or @MyCode like '%[I,O]%'

set @MyCode = left(convert(varchar(40), newid()), 6)

select @MyCode

paulhumphris Hall of Fame Points: 3496 More actions · Answer 8

Sorry oversight in previous post that newid() will generate a hex value (i.e. only A-F).

You can use a view to generate the random number and a generic function to construct a random string of alphanumeric characters or any length and excluding any set of character (in this case I & O).

create view v_RandomNumber

as

select rand() as Value

go

create function dbo.funRandChar(@Length int, @Exclude varchar(36))

returns varchar(1000)

as

begin

declare @result varchar(1000),

@charval int

set @result = ''

while len(@result) != @length

begin

while @charval is null or char(@charval) like '[' + @Exclude + ']'

begin

set @charval = (select convert(int, Value * 36) from v_RandomNumber)

if @charval < 10

set @charval = @charval + 48

else

set @charval = @charval + 55

end

set @result = @result + char(@charval)

set @charval = null

end

return(@result)

end

go

-- return a 6 character random string excluding I & O

select dbo.funRandChar(6, 'IO')

David Burrows SSC Guru Points: 65142 More actions · Answer 9

A possible alternative

DECLARE @chars varchar(255), @code varchar(6)

SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'

SET @code = ''

SELECT @code = @code + [char]

FROM

(SELECT TOP 6 SUBSTRING(@chars,number,1) AS [char]

FROM master.dbo.spt_values WHERE type = 'P' AND number between 1 AND LEN(@chars)

ORDER BY NEWID()) x

SELECT @code

(adjust @chars to limited chars used)

Far away is close at hand in the images of elsewhere.
Anon.