MySQL Forums
Forum List  »  Stored Procedures

Re: Dynamic Sql Statement in SP
Posted by: Emma Middlebrook
Date: January 11, 2006 03:34PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2754
January 09, 2006 06:22AM
1480
January 09, 2006 08:06AM
1218
January 09, 2006 02:10PM
1798
January 09, 2006 08:02PM
1681
January 10, 2006 12:24AM
1452
January 10, 2006 03:07AM
1669
January 10, 2006 06:51AM
1394
January 10, 2006 01:49PM
1740
January 10, 2006 06:35PM
1545
January 10, 2006 07:22PM
1630
January 10, 2006 10:33PM
1626
January 11, 2006 03:10AM
2197
January 11, 2006 04:27AM
1652
January 11, 2006 06:54AM
Re: Dynamic Sql Statement in SP
1476
January 11, 2006 03:34PM
1647
January 11, 2006 03:56PM
1635
January 12, 2006 03:20AM
1445
January 12, 2006 04:44AM
1414
January 11, 2006 09:08PM
1662
January 12, 2006 03:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.