Dynamic Sql Statement in SP

[Ming Yeung]

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