Re: Dynamic Sql Statement in SP
Roland Bouman wrote:
> Better is to avoid it by explicitly using
> parameters:
>
> declare v_paramcount int unsigned default 0;
> set @param_username := NULL;
> set @param_role := NULL;
> set @param_status := NULL;
> ...
> ...
> IF (@varSql <> '') THEN
> SET @varSql = CONCAT(@varSql, ' AND ');
> END IF;
>
> IF inUsername IS NULL
> SET @varSql := CONCAT(@varSQL,"? is NULL");
> ELSE
> SET @param_username =
> conct('%',inUsername,'%');
> SET @varSql := CONCAT(@varSQL,"username =
> ?");
> END IF;
> ..
> ..
> PREPARE stmt from @Sql;
> EXECUTE stmt USING @param_username, @param_role,
> @param_status;
>
Hi,
I am trying to execute a stored procedure where the syntax is created dynamically in the stored procedure dependent on what parameters are supplied. I wanted to have the parameters sent in as NULL for columns that did not need updating and I was wondering if there was a way of using your technique above for that?
I had originally done as Ming had by concatenating the columns and update values and I'd like to be a little more secure if I could.
Here is the stored proc:
PROCEDURE `db_scribbles`.`sp_UpdateAuthor`(IN author_id INTEGER, IN firstname VARCHAR(50), IN surname VARCHAR(50), IN personal_webpage LONGTEXT ...)
SQL SECURITY INVOKER
COMMENT 'Update an author in the database'
BEGIN
DECLARE @sql_command VARCHAR(200)
SET @sql_command = "UPDATE tbl_author";
IF firstname IS NOT NULL THEN
SET @sql_command = CONCAT(@sql_command, ", Firstname =", firstname);
END IF
IF surname IS NOT NULL THEN
SET @sql_command = CONCAT(@sql_command, ", Surname =", surname);
END IF
IF personal_webpage IS NOT NULL THEN
SET @sql_command CONCAT(@sql_command, ", Personal_webpage =", personal_webpage);
END IF
PREPARE STMT FROM @sql_command;
EXECUTE STMT;
END
There are certainly things wrong with it as it doesn't even like my declare line for the variable.
Thanks,
Emma