Optional Arguments in Stored Proc for Row Update?

Grofit

Hi there!

On a stored proc im working on im going to need to update data but the user may only send over 2 out of the 5 possible column changes over, so i was wondering if there was some way to do optional arguments in MySQL Stored Procs and then somehow ignore the columns that are not included...

Here is some psudo code to try and show what i mean:


DoUpdate(Column1 INT DEFAULT NULL, Column2 TEXT DEFAULT NULL, Column3 INT DEFAULT NULL, ID INT)

Begin

UPDATE random_table 

IF(Column1 != NULL)
 SET column_definition_one = Column1;
END IF

IF(Column2 != NULL)
 SET column_definition_two = Column2;
END IF

IF(Column3 != NULL)
 SET column_definition_three = Column3;
END IF

WHERE id = ID;

End

As its in a stored proc i dont think there is a way to break apart the SQL like shown, but thats the sort of functionality im going to be after. So depending on the arguments you can constrain the updates that take place?

If anyone has any advice then please shoot it my way 😃

CoderDan

I think you'll find, unfortunately, that most of us, as primarily php coders, don't use mysql stored procs, and those of us that do aren't terribly proficient with them. If you don't get an answer here today, I'd recommend checking some more specific mysql based forums for help.

Hopefully I'm wrong, and someone will have your answer nice and quick, but to get the result's you're asking about I'd just be using plain php and straight sql queries, and wouldn't bother with procs.

Grofit

hey,

im currently moving 99% of my dynamic sql stuff over to stored procs, so im trying to avoid having raw SQL in the code... its not the end of the world if i cant do this, i just dont want to be sending over arguments that dont need to be sent... especially text based arguments...