title: Generating random code per client ID
body: <p>I want to be able to generate a random &amp; unique code for a given client ID a given number of times. The code I have below <em>nearly</em> fulfils this objective perfectly, I say only nearly because the call to the RAND() function is not updated, the value is the same regardless. I guess you could say then the code fails to fulfil this objective. </p>
  
  <p>The code must be 2 banks of 4 digits separated by a hyphen so 0123-4567 for example. I need the possibility of the code beginning with a zero and must only contain numeric values other than the separator. </p>
  
  <p>Other things you may need to know are: the client ID is a uniqueidentifier. The FriendlyName and FriendlyKey values must be unique. Codes will generally be generated in batches of 50. The random element of this must be included to try and prevent any guessing of valid codes. Each code has its own primary key (uniqueidentifier).</p>
  
  <p>I understand that as this process matures the possiblity of generating a duplicate code will increase but given that the codes are generated against a client ID, there are 100 million combinations (0000-0000 - 9999-9999) available for each client and for the purposes of this exercise, this possibility isn't a consideration. Also, the time needed to check against the existing dataset is going to increase but again, for the purposes of this exercise, this consideration can be safely ignored.</p>
  
  <p>Thanks for reading. Hope someone can help.</p>
  
  <pre><code>CREATE PROCEDURE dbo.GenerateAccessCodeForClient
  @clientID uniqueidentifier,
  @total int
  
  as
  
  DECLARE @numCodes INT = @total;
  DECLARE @startNum INT = 1;
  DECLARE @nums TABLE(n NVARCHAR(9) NOT NULL);
  DECLARE @results TABLE(Code NVARCHAR(9) NOT NULL, ID UNIQUEIDENTIFIER NOT NULL, CLIENTID UNIQUEIDENTIFIER NOT NULL);
  
  INSERT INTO @nums
  SELECT TOP (@numCodes) RIGHT('0' + CAST(ROW_NUMBER() OVER (ORDER BY s1.[object_id]) + (@startNum - 1) AS NVARCHAR), 9) n
  FROM sys.all_objects s1
            CROSS JOIN sys.all_objects s2;
  
  INSERT [AccessCodes]([PrimaryKey], [Code], [FriendlyKey], [FriendlyName], [AdditionalInfo], [CreatedDate], [ModifiedDate], [CreatedByID], [ModifiedByID], [ClientID])
  OUTPUT Inserted.Code, Inserted.PrimaryKey, Inserted.ClientID
  INTO @results
  select NEWID(), 
      concat(LEFT(SUBSTRING (RTRIM(RAND()) + SUBSTRING(RTRIM(RAND()),3,11), 3,11),4) , '-', LEFT(SUBSTRING (RTRIM(RAND()) + SUBSTRING(RTRIM(RAND()),3,11), 3,11),4)), 
       n, 
       'fName' + n, 
       'addInfo' + n, 
       getdate(), 
       getdate(), 
       'ffffffff-ffff-ffff-ffff-ffffffffffff',
       'ffffffff-ffff-ffff-ffff-ffffffffffff',
       'ffffffff-ffff-ffff-ffff-ffffffffffff'
  FROM @nums;
  
  SELECT Code, ID, CLIENTID
  FROM @results
  ORDER BY Code DESC;
  </code></pre>