MySQL Forums
Forum List  »  Stored Procedures

Dynamic Sql Statement in SP
Posted by: Ming Yeung
Date: January 09, 2006 06:22AM

Hello,

I have been working on this for ages, but still no luck. I believe many newbies have the same problem with the MySQL Stored Procedure... I have the SP as below:

CREATE PROCEDURE `Search`(IN inUsername VARCHAR(20), IN inRole CHAR, IN inStatus CHAR, etc...)
BEGIN

SET @varSqlPrefix = "SELECT * FROM admin";
SET @varSql = ' ';

IF inUsername IS NOT NULL THEN

IF @varSql != ' ' THEN
SET @varSql = CONCAT(@varSql, " AND");
END IF;

SET @varSql = CONCAT(@varSql, " username='", inUsername, "'");

END IF;

IF inRole IS NOT NULL THEN

IF @varSql != ' ' THEN
SET @varSql = CONCAT(@varSql, " AND");
END IF;

SET @varSql = CONCAT(@varSql, " role='", inRole, "'");

END IF;

...etc ... so on...

IF @varSql IS NOT NULL THEN
SET @varSql = CONCAT(' WHERE', @varSql);
END IF;

SET @fullSQL = CONCAT(@varSqlPrefix, @varSql);

PREPARE STMT FROM @fullSQL;
EXECUTE STMT;

END

This SP gets the pass-in variables and generate a dynamic Sql statement depends on the pass-in variables, as you can see. I have been searching around on google, but with no luck as SP in MySQL is still new.

What do I actually do?

Thanks guys.
Ming

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamic Sql Statement in SP
2753
January 09, 2006 06:22AM
1480
January 09, 2006 08:06AM
1218
January 09, 2006 02:10PM
1797
January 09, 2006 08:02PM
1680
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
1474
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.