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