CODEWITHSUNDEEP
mysqlsqlstored-procedures
user1052610
user1052610

Reputation: 4719

How to include conditional logic in a prepared statement in a Stored Procedure

We have a MySql stored procedure which includes the following:

 SET @queryString = (SELECT CONCAT("UPDATE Table2 t ",
 "JOIN vTable1 v ",
"ON t.id.=v.id ",
 "SET t.sub_id_track = 1, t.prefix = '", newPrefix,"',",
 " t.sub_id = ", vIndex,
 ";"));
 
 PREPARE update_sql FROM @queryString;

 EXECUTE update_sql;

This works well.

We now need to change it so that

If newPrefix is contains any value, then

SET t.sub_id_track = 1

but if newPrefix is null or empty, then

SET t.sub_id_track = 0

What is the correct way to do this?

Thanks

Upvotes: 1

Views: 87

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562671

First, I have a few comments:

  • Your prepared query has an SQL injection vulnerability.
  • There's no reason you need to run this as a prepared query in the first place.

I suggest this alternative:

UPDATE Table2 t
JOIN vTable1 v ON t.id = v.id
SET t.sub_id_track = IFNULL(NULLIF(newPrefix, ''), 1, 0), 
    t.prefix = newPrefix, 
    t.sub_id = vIndex;

No PREPARE/EXECUTE required. No SQL injection vulnerability. And it handles the conditional you described.

But I wonder if you intended there to be a WHERE clause in there? This will apply the UPDATE to every row.

Upvotes: 1

Related Questions