Hi Emma,
I think you can do this without dynamic SQL. It's certainly what I would try.
First, you need to prefix the parameternames, so you can distinguish between them and the columns you're updating. I like a p_ for that, but you can use whatever you like best:
PROCEDURE `db_scribbles`.`sp_UpdateAuthor`(
IN p_author_id INTEGER
, IN p_firstname VARCHAR(50)
, IN p_surname VARCHAR(50)
, IN p_personal_webpage LONGTEXT
)
UPDATE tbl_author
SET firstName = COALESCE(p_firstname,firstName)
, surName = COALESCE(p_surName,surName)
, personal_webpage= COALESCE(p_personal_webpage,personal_webpage)
WHERE author_id = p_author_id
(I'm gueassing you intended to include the WHERE clause, you certainly don't want to update *ALL* the records in the author table, right?)
You see, COALESCE() takes two or more parameters, and returns the value of the first (reading from left to right) NOT NULL parameter. In this statement, the columns will be updated with the corresponding parameter, but if the parameter is in fact NULL, the current column value is assigned.
See:
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html for more info on COALESCE().
(MySQL is even so smart that it wont even touch a particular field in a record during an update if the current value equals the assigned value; see:
http://dev.mysql.com/doc/refman/5.0/en/update.html. Of course, the result would be exactly the same even if it would re-assign the current value; either way it should solve your problem)
BTW, the reason your original syntax is rejected is indeed to be found in the DECLARE line. You see, the @ in front of the variable name tells MySQL that you are referring to a so-called user variable (as Ming points out, user variable is actually a bit of a misnomer, see the previous posts in this thread). Well, you can't declare those explicitly. You can only declare local variables this way. For more info on this, read (or reread) this post:
http://forums.mysql.com/read.php?98,62373,62512#msg-62512