CODEWITHSUNDEEP
mysql
GreetRufus
GreetRufus

Reputation: 431

MYSQL SP not looping

I found a great SQL statement that will mask production data (Howto generate meaningful test data using a MySQL function).

I am attempting to add this as a stored procedure.

When i run the function from a query, it works perfectly.

However, when i call the function from a SP, the records are randomized, but with the same data. How do I get the SP to randomized each record like the query call????

So if I run this:

UPDATE MAINTBL 
  SET first_name = (SELECT str_random('Cc{3}c(4)')),
    last_name = (SELECT str_random('Cc{5}c(6)')),
    email = (SELECT str_random('c{3}c(5)[.|_]c{8}c(8)@[google|yahoo|live|mail]".com"')),

I get this:

first_name || last_name  || email
===========================================================
Xstpvb     || Slbynwcigi || [email protected]
Vohv       || Nyeaqbn    || [email protected]
Nkjpxqm    || Ogrosyjwi  || [email protected]
Mdig       || Cccepmxlla || [email protected]

But when Iput it in a SP:

DROP PROCEDURE IF EXISTS mask_data;

CREATE PROCEDURE mask_data()
  BEGIN
    UPDATE MAINTBL 
      SET first_name = (SELECT str_random('Cc{3}c(4)')),
        last_name = (SELECT str_random('Cc{5}c(6)')),
        email = (SELECT str_random('c{3}c(5)[.|_]c{8}c(8)@[google|yahoo|live|mail]".com"')),
END;

Then call it from a query:
    call mask_data;

I get the same output in each record:

first_name || last_name  || email
Ofgtlmz    || Mndbynbepn || [email protected]
Ofgtlmz    || Mndbynbepn || [email protected]
Ofgtlmz    || Mndbynbepn || [email protected]
Ofgtlmz    || Mndbynbepn || [email protected]

What am I doing wrong???

Upvotes: 1

Views: 66

Answers (1)

Rick James
Rick James

Reputation: 142298

(to get this out of Unanswered)

Fixed by removing unnecessary SELECTs.

Upvotes: 1

Related Questions