By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,743 Members | 1,058 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,743 IT Pros & Developers. It's quick & easy.

SP Generate Random String - Check if String Already Exists

P: 10
I found a stored procedure online a while back and want to inplement it. The only problem is that it doesn't check to see if the number generated is currently in use in the DB.

I know it's really unlikely for the same number to be generated twice (with a 10 digit number) but it's for a shopping cart so I need it to be 100% reliable.

I thought about opening a cursor then looping through each distinct record in the DB but couldn't figure out how I'd restart the procedure if a duplicate was found.

The table for lookup is "PurchaseHist" and the field is "OrderNum". I'd appriciate any help with this.


Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. ALTER procedure [dbo].[GenerateRandomString] (
  6.  @useNumbers bit,
  7.  @useLowerCase bit,
  8.  @useUpperCase bit,
  9.  @charactersToUse as varchar(100),
  10.  @passwordLength as smallint,
  11.  @password varchar(100) OUT
  12. )
  13. As
  14. Begin
  15.  
  16. if @passwordLength <= 0
  17.  raiserror('Cannot generate a random string of zero length.',16,1)
  18.  
  19. declare @characters varchar(100)
  20. declare @count int
  21.  
  22. set @characters = ''
  23.  
  24. if @useNumbers = 1
  25. begin
  26.  -- load up numbers 0 - 9
  27.  set @count = 48
  28.  while @count <=57
  29.  begin
  30.      set @characters = @characters + Cast(CHAR(@count) as char(1))
  31.      set @count = @count + 1
  32.  end
  33. end
  34.  
  35. if @useLowerCase = 1
  36. begin
  37.  -- load up uppercase letters A - Z
  38.  set @count = 65
  39.  while @count <=90
  40.  begin
  41.      set @characters = @characters + Cast(CHAR(@count) as char(1))
  42.      set @count = @count + 1
  43.  end
  44. end
  45.  
  46. if @useUpperCase = 1
  47. begin
  48.  -- load up lowercase letters a - z
  49.  set @count = 97
  50.  while @count <=122
  51.  begin
  52.      set @characters = @characters + Cast(CHAR(@count) as char(1))
  53.      set @count = @count + 1
  54.  end
  55. end
  56.  
  57. set @count = 0
  58. set @password = ''
  59.  
  60. -- If you specify a character set to use, the bit flags get ignored.
  61. if Len(@charactersToUse) > 0
  62. begin
  63.  while charindex(@charactersToUse,' ') > 0
  64.  begin
  65.   set @charactersToUse = replace(@charactersToUse,' ','')
  66.  end
  67.  
  68.  if Len(@charactersToUse) = 0
  69.   raiserror('Cannot use an empty character set.',16,1)
  70.  
  71.  while @count <= @passwordLength
  72.  begin
  73.      set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+1,1)
  74.      set @count = @count + 1
  75.  end
  76. end
  77. else
  78. begin
  79.  while @count <= @passwordLength
  80.  begin
  81.      set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
  82.      set @count = @count + 1
  83.  end
  84. end
  85.  
  86. end
Jan 27 '08 #1
Share this Question
Share on Google+
5 Replies


Jim Doherty
Expert 100+
P: 897
Your method of determining whether the value you seek is in the database should ultimately be along the lines of SELECTING ...IF... it EXISTS. This method stops processing at the first value it encounters returning true if so encountered.. That gives you your logic for proceeding and is more efficient than counting records in a table to see if your value exists.

Example

Expand|Select|Wrap|Line Numbers
  1. IF EXISTS (select *
  2. from YourTableName
  3. where YourFieldName = 'YourTextvalue')
  4. Print 'Record exits'
  5. ELSE
  6. Print 'Record doesn''t exist'
  7.  
Regards

Jim :)
Jan 28 '08 #2

P: 10
Thanks, I'm pretty sure I got it rocking!

Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. ALTER procedure [dbo].[GenerateRandomString] (
  6.  @useNumbers bit,
  7.  @useLowerCase bit,
  8.  @useUpperCase bit,
  9.  @charactersToUse as varchar(100),
  10.  @passwordLength as smallint,
  11.  @password varchar(100) OUT
  12. )
  13. As
  14. Begin
  15.  
  16. declare @unique int
  17. set @unique = 0
  18.  
  19. while @unique = 0
  20. begin
  21.  
  22.     if @passwordLength <= 0
  23.      raiserror('Cannot generate a random string of zero length.',16,1)
  24.  
  25.     declare @characters varchar(100)
  26.     declare @count int
  27.  
  28.     set @characters = ''
  29.  
  30.     if @useNumbers = 1
  31.     begin
  32.      -- load up numbers 0 - 9
  33.      set @count = 48
  34.      while @count <=57
  35.      begin
  36.          set @characters = @characters + Cast(CHAR(@count) as char(1))
  37.          set @count = @count + 1
  38.      end
  39.     end
  40.  
  41.     if @useLowerCase = 1
  42.     begin
  43.      -- load up uppercase letters A - Z
  44.      set @count = 65
  45.      while @count <=90
  46.      begin
  47.          set @characters = @characters + Cast(CHAR(@count) as char(1))
  48.          set @count = @count + 1
  49.      end
  50.     end
  51.  
  52.     if @useUpperCase = 1
  53.     begin
  54.      -- load up lowercase letters a - z
  55.      set @count = 97
  56.      while @count <=122
  57.      begin
  58.          set @characters = @characters + Cast(CHAR(@count) as char(1))
  59.          set @count = @count + 1
  60.      end
  61.     end
  62.  
  63.     set @count = 0
  64.     set @password = ''
  65.  
  66.     -- If you specify a character set to use, the bit flags get ignored.
  67.     if Len(@charactersToUse) > 0
  68.     begin
  69.      while charindex(@charactersToUse,' ') > 0
  70.      begin
  71.       set @charactersToUse = replace(@charactersToUse,' ','')
  72.      end
  73.  
  74.      if Len(@charactersToUse) = 0
  75.       raiserror('Cannot use an empty character set.',16,1)
  76.  
  77.      while @count <= @passwordLength
  78.      begin
  79.          set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+1,1)
  80.          set @count = @count + 1
  81.      end
  82.     end
  83.     else
  84.     begin
  85.      while @count <= @passwordLength
  86.      begin
  87.          set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
  88.          set @count = @count + 1
  89.      end
  90.     end
  91.  
  92.     IF EXISTS (select *
  93.     from datakno_harrisgolf.PurchaseHist
  94.     where OrderNum = @password)
  95.     set @unique = 0
  96.     ELSE
  97.     set @unique = 1
  98. end
  99. end
Jan 28 '08 #3

Jim Doherty
Expert 100+
P: 897
Thanks, I'm pretty sure I got it rocking!

Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. ALTER procedure [dbo].[GenerateRandomString] (
  6. @useNumbers bit,
  7. @useLowerCase bit,
  8. @useUpperCase bit,
  9. @charactersToUse as varchar(100),
  10. @passwordLength as smallint,
  11. @password varchar(100) OUT
  12. )
  13. As
  14. Begin
  15.  
  16. declare @unique int
  17. set @unique = 0
  18.  
  19. while @unique = 0
  20. begin
  21.  
  22.     if @passwordLength <= 0
  23.      raiserror('Cannot generate a random string of zero length.',16,1)
  24.  
  25.     declare @characters varchar(100)
  26.     declare @count int
  27.  
  28.     set @characters = ''
  29.  
  30.     if @useNumbers = 1
  31.     begin
  32.      -- load up numbers 0 - 9
  33.      set @count = 48
  34.      while @count <=57
  35.      begin
  36.          set @characters = @characters + Cast(CHAR(@count) as char(1))
  37.          set @count = @count + 1
  38.      end
  39.     end
  40.  
  41.     if @useLowerCase = 1
  42.     begin
  43.      -- load up uppercase letters A - Z
  44.      set @count = 65
  45.      while @count <=90
  46.      begin
  47.          set @characters = @characters + Cast(CHAR(@count) as char(1))
  48.          set @count = @count + 1
  49.      end
  50.     end
  51.  
  52.     if @useUpperCase = 1
  53.     begin
  54.      -- load up lowercase letters a - z
  55.      set @count = 97
  56.      while @count <=122
  57.      begin
  58.          set @characters = @characters + Cast(CHAR(@count) as char(1))
  59.          set @count = @count + 1
  60.      end
  61.     end
  62.  
  63.     set @count = 0
  64.     set @password = ''
  65.  
  66.     -- If you specify a character set to use, the bit flags get ignored.
  67.     if Len(@charactersToUse) > 0
  68.     begin
  69.      while charindex(@charactersToUse,' ') > 0
  70.      begin
  71.      set @charactersToUse = replace(@charactersToUse,' ','')
  72.      end
  73.  
  74.      if Len(@charactersToUse) = 0
  75.      raiserror('Cannot use an empty character set.',16,1)
  76.  
  77.      while @count <= @passwordLength
  78.      begin
  79.          set @password = @password + SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@charactersToUse)+1,1)
  80.          set @count = @count + 1
  81.      end
  82.     end
  83.     else
  84.     begin
  85.      while @count <= @passwordLength
  86.      begin
  87.          set @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
  88.          set @count = @count + 1
  89.      end
  90.     end
  91.  
  92.     IF EXISTS (select *
  93.     from datakno_harrisgolf.PurchaseHist
  94.     where OrderNum = @password)
  95.     set @unique = 0
  96.     ELSE
  97.     set @unique = 1
  98. end
  99. end
Thats great...thanks for replying

Jim :)
Jan 28 '08 #4

P: 10
Oops. The SELECT statement to find a record should've been

Expand|Select|Wrap|Line Numbers
  1. IF EXISTS (select OID --Can't select *...
  2. from datakno_harrisgolf.PurchaseHist
  3. where OrderNum = @password)
  4. set @unique = 0
  5. ELSE
  6. set @unique = 1
  7.  
Feb 1 '08 #5

P: 10
I appologize... By selecting just the field, it stopps working. I thought it would be a drag on the database by selecting * (all).
Feb 1 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.