Using crypt_gen_random to generate unique Serial Numbers

Question

Using crypt_gen_random to generate unique Serial Numbers

iviewtheworldthroughmylens

Old Hand

Points: 334
More actions
November 14, 2019 at 10:34 am

#3697600
Trying to generate unique serial numbers with alphanumerics (A-Z,a-z,0-9) and no special characters.Used below code where sLength was min 10 and max 12 as defined in front end.
```
declare @sLength tinyint
declare @randomString varchar(50)
declare @counter tinyint
declare @nextChar char(1)
declare @rnd as float

set @sLength = 3
set @counter = 1
set @randomString = ''

while @counter <= @sLength
begin
-- crypt_gen_random produces a random number. We need a random 
-- float.
select @rnd = cast(cast(cast(crypt_gen_random(2) AS int) AS float) / 
65535 as float) 
select @nextChar = char(48 + convert(int, (122-48+1) * @rnd))
if ascii(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
begin
select @randomString = @randomString + @nextChar
set @counter = @counter + 1
end
end
select @randomString
```
Now the requirement has changed where we will be sending set of characters(min 6) & numbers(min 2) selected in front end. So i will be sending the parameter as follows @Include = 'ABCDEFG12345' and @Exclude='HIJKLMNOPQRSTUVXYZ06789'. Can someone suggest how do i change the below code as per the requirement.
My idea is to change the if ascii(@nextChar) not in line and add all the Ascii codes of the @Exclude characters & numbers, but not able to write the code for the same.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You can simplify by using a set-based approach instead of a loop. I used NEWID instead of CRYPT_GEN_RANDOM, although I don't suppose it makes much difference. Not sure why you'd have an @Include and @Exclude variable?

DECLARE @sLength tinyint = 12;
DECLARE @Include varchar(62) = 'ABCDEFG12345';

WITH N4 AS (
SELECT n FROM (VALUES (1),(2),(3),(4))v(n)
) 
, N16 AS (
SELECT ROW_NUMBER() OVER (ORDER BY n1.n) AS n
FROM N4 n1
CROSS JOIN N4 n2
)
SELECT SUBSTRING(@Include, CAST(CEILING(RAND(CHECKSUM(NEWID())) * LEN(@Include)) AS tinyint), 1)
FROM N16
WHERE n<= @sLength
FOR XML PATH ('');

John

iviewtheworldthroughmylens Old Hand Points: 334 More actions · Answer 2

Can you pls explain the below code

WITH N4 AS (
SELECT n FROM (VALUES (1),(2),(3),(4))v(n)
) 
, N16 AS (
SELECT ROW_NUMBER() OVER (ORDER BY n1.n) AS n
FROM N4 n1
CROSS JOIN N4 n2
)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

It's a virtual tally table. If you put SELECT n FROM N16 at the end of the code, and run it, you'll see what it does. Tally tables can be used to replace loops, thus making your code much more efficient. If you're not familiar with this concept, please search this site for articles by Jeff Moden, who has written extensively on the subject.

John

iviewtheworldthroughmylens Old Hand Points: 334 More actions · Answer 4

Thank you John will look into it.

For the above code i would like to add a small insert code for the generated serial numbers. Suppose for a medicine Batchno Qty is 100 which I enter in frond end i need to create 100 serial numbers with length of serial number being 3.

@sLength=3, length of serial number

@Include='ABC' ( with this input we can max generate 27 diff serial numbers)

so just for testing purpose i have kept the @BatchQuantity=10.

Its not generating unique serial numbers.

Create proc dbo.InsertSerialNumbers
@sLength int,
@Include varchar(500),
@BatchQuantity int
As
begin

Declare @Counter int
declare @randomString varchar(100)
set @Counter=1

while @Counter <= @BatchQuantity
begin

WITH N4 AS (
SELECT n FROM (VALUES (1),(2),(3),(4))v(n)
) 
, N16 AS (
SELECT ROW_NUMBER() OVER (ORDER BY n1.n) AS n
FROM N4 n1
CROSS JOIN N4 n2
)

insert into DummySerialNumberTable(Serialnumbers)values(
(SELECT SUBSTRING(@Include, CAST(CEILING(RAND(CHECKSUM(NEWID())) * LEN(@Include)) AS tinyint), 1)
FROM N16
WHERE n<= @sLength
FOR XML PATH ('')))
set @Counter=@Counter+1

End

End

Below is the result

BAA

ABA

BAA

BCC

ACC

BAB

CBB

BAC

ACC

AAB

ABC

BAA

ACC

CAA

BAB

CCB

ACC

CBA

ABA

BBA

ACB

BCC

ABB

BCB

CBB

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Slightly more complicated, but doable. The tally table comes to the rescue again. We use it twice - once to get multiple characters in our random string, and once to get multiple random strings. Notice I changed it from N16 to N100 since it now needs to have more numbers in it to accommodate the new requirement. You also need to include the value of n from the outer tally table in the argument for the RAND function so that you don't get the same value 100 times.

DECLARE @sLength tinyint = 10;
DECLARE @Include varchar(62) = 'ABCDEFG12345';
DECLARE @Quantity tinyint = 100;

WITH N10 AS (
SELECT n FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0))v(n)
) 
, N100 AS (
SELECT ROW_NUMBER() OVER (ORDER BY n1.n) AS n
FROM N10 n1
CROSS JOIN N10 n2
)
SELECT r.RandString
FROM N100 n101
CROSS APPLY (
SELECT SUBSTRING(@Include, CAST(CEILING(ABS(RAND(CHECKSUM(NEWID())-n101.n)) * LEN(@Include)) AS int), 1)
FROM N100 n102
WHERE n102.n<= @sLength
FOR XML PATH ('')
) r(RandString)
WHERE n101.n <= 100;

John