Using crypt_gen_random to generate unique Serial Numbers

  • iviewtheworldthroughmylens

    SSC Veteran

    Points: 274

    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.

     

  • John Mitchell-245523

    SSC Guru

    Points: 148434

    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

    SSC Veteran

    Points: 274

    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: 148434

    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

    SSC Veteran

    Points: 274

    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

    ABA

    BBA

    ACB

    BCC

    ABB

    BCB

    CBB

  • John Mitchell-245523

    SSC Guru

    Points: 148434

    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

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

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