1

I am looking for a flexible update stored procedure. I made this one. When i call this CALL spUpdatePage(1,'a','b','c''d') it works fine.

Do i have to pass always in this example 5 params?

Can i also do this 

CALL spUpdatePage(1,'a','c')

and that the stored procedure knows that i only want to update the columns name and description? 

CREATE DEFINER=`root`@`localhost` PROCEDURE `spUpdatePage`(
IN `pKey` INT(4), 
IN `name` VARCHAR(255), 
IN `title` VARCHAR(255), 
IN `description` VARCHAR(255), 
IN `keywords` VARCHAR(255))
UPDATE
    pages
SET
    name = COALESCE(name, name),
    title = COALESCE(title, title),
    description = COALESCE(description, description),
    keywords = COALESCE(keywords, keywords)
WHERE
    id = pKey
Bas
  • 137
  • 11
  • possible duplicate of [Is it possible to have a default parameter for a mysql stored procedure?](http://stackoverflow.com/questions/982798/is-it-possible-to-have-a-default-parameter-for-a-mysql-stored-procedure) – RandomSeed Jul 28 '14 at 08:46

1 Answers1

1

Do I have to pass always in this example 5 params?

Can I also do this?

CALL spUpdatePage(1,'a','c')

For your posted stored procedure, you must pass all 5 parameters. No, you can't just pass name and description (to your posted stored procedure). However, you could write a stored procedure that only updates name and description. Something like,

CREATE DEFINER=`root`@`localhost` PROCEDURE `spUpdatePageNameTitle`(
  IN `pKey` INT(4), 
  IN `name` VARCHAR(255), 
  IN `title` VARCHAR(255))
UPDATE
  pages
SET
  name = COALESCE(name, name),
  title = COALESCE(title, title)
WHERE
  id = pKey
Elliott Frisch
  • 198,278
  • 20
  • 158
  • 249
  • Stupid question of me. Doesnt work. So Stored Procedure isnt flexible... So you have to post always all params... Thnx! – Bas Jul 27 '14 at 07:33
  • @Bas See also [this question and answer](http://stackoverflow.com/a/982824/2970947). – Elliott Frisch Jul 27 '14 at 07:36