• 1

• 1.3k
• 10
• 8

#Randomness#

Using NEWID() seems like a pretty smart way of getting random records, and it is. There is however a better function for this purpose. Say hello to the CRYPT_GEN_RANDOM() function.

The CRYPT_GEN_RANDOM() has a parameter that we need to provide, the length of the random string it is going to generate. For what we need, 4 or 5 characters is plenty.

I will update my answer with the proof when I find the time, but my own testing found CRYPT_GEN_RANDOM() generates less predictable records than NEWID().

#The Random Stored Procedure#

I have no idea what your Random stored procedure entails, but might I suggest an alternate method of generating a random number...

WITH Numbers(I) AS
(
 SELECT 1000
 
 UNION ALL 
 
 SELECT I+1
 FROM Numbers
 WHERE I<5000
)
SELECT TOP 1 @amount = I
FROM Numbers
ORDER BY CRYPT_GEN_RANDOM(4)
OPTION (MAXRECURSION 0)

First, this generates a table of numbers 1000 to 5000 using a recursive CTE, then selects one randomly using the CRYPT_GEN_RANDOM() that we discussed earlier.

#To If or Not to If#

Might I also suggest an alternate method of generating the @payment_way. Instead of using an If, might I suggest using a Simple Case Statement.

Personally I find this

SET @payment_way = 
CASE @amount % 2
 WHEN 1
 THEN 'cash'
 ELSE 'credit card'
END

To be a more SQL friendly way of writing it.

#Putting it all together#

This is what I ended up with. It's not any smaller, but it doesn't rely on any external stored procedures.

ALTER PROCEDURE generate_invoices
AS
BEGIN
 DECLARE 
 @id_employee INT = (SELECT TOP 1 id_employee FROM Employee ORDER BY CRYPT_GEN_RANDOM(4)),
 @id_client INT = (SELECT TOP 1 id_client FROM Client ORDER BY CRYPT_GEN_RANDOM(4)),
 @id_service INT = (SELECT TOP 1 id_service FROM Service ORDER BY CRYPT_GEN_RANDOM(4)),
 @amount INT,
 @payment_way VARCHAR(20)
 SET @id_employee 
 SET @id_client 
 SET @id_service 
 WITH Numbers(I) AS
 (
 SELECT 1000
 
 UNION ALL 
 
 SELECT I+1
 FROM Numbers
 WHERE I<5000
 )
 SELECT TOP 1 @amount = I
 FROM Numbers
 ORDER BY CRYPT_GEN_RANDOM(4)
 OPTION (MAXRECURSION 0)
 SET @payment_way = 
 CASE @amount % 2
 WHEN 1
 THEN 'cash'
 ELSE 'credit card'
 END
 INSERT INTO Invoice VALUES (@id_employee, @id_clienta, @id_service, @amount, @payment_way)
END
GO