current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 6 down vote favorite

I'm doing a project in ms sql and I have to fill table with random records.

Here is a definition of table:

create table invoice(
    id_invoice INT PRIMARY KEY IDENTITY(1,1),
    id_employee INT FOREIGN KEY REFERENCES Employee(id_employee),
    id_client INT FOREIGN KEY REFERENCES Client(id_client),
    id_service INT FOREIGN KEY REFERENCES Service(id_service),
    amount MONEY,
    payment_way VARCHAR(20) CHECK (payment_way in ('cash', 'credit_card'))
);

And here is the code that generates random records:

ALTER PROCEDURE generate_invoices
AS
BEGIN
    DECLARE @id_employee INT
    DECLARE @id_client INT
    DECLARE @id_service INT
    DECLARE @amount INT
    DECLARE @payment_way VARCHAR(20)

    SET @id_employee = (SELECT TOP 1 id_employee FROM Employee ORDER BY NEWID())
    SET @id_client = (SELECT TOP 1 id_client FROM Client ORDER BY NEWID())
    SET @id_service = (SELECT TOP 1 id_service FROM Service ORDER BY NEWID())
    EXEC random 1000, 5000, @amount OUTPUT
    IF (@amount % 2 = 1) SET @payment_way = 'cash'
    ELSE SET @payment_way = 'credit card'
    INSERT INTO Invoice VALUES (id_employee, @id_clienta, @id_service, @amount, @payment_way)
END
GO

EXEC generate_invoices
GO 10

The code works obviously, but I would like to ask if there is better way (shorter, more efficient) to do same thing, because I must write over a dozen of similar procedure for other tables.

share|improve this question
    
Interesting and clever. I didn't realize that ORDER BY could be exploited that way. I'm looking forward to seeing what others have to say. –  RubberDuck Feb 3 at 1:30
1  
@RubberDuck See What is the best way to get a random ordering?. –  200_success♦ Feb 3 at 16:43

1 Answer 1

up vote 4 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.