Re: Dynamic Sql Statement in SP

[Ming Yeung]

Thanks for both of your comments, Stefano & Roland.
I have figured out a simple working solution based on your comments.

CREATE PROCEDURE `Search`(IN inUsername VARCHAR(20), IN inRole CHAR, IN inStatus CHAR)
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 LIKE '%", 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;

IF inStatus IS NOT NULL THEN

IF (@varSql <> '') THEN
SET @varSql = CONCAT(@varSql, ' AND');
END IF;

SET @varSql = CONCAT(@varSql, " status='", inStatus, "'");

END IF;

IF (@varSql <> '') THEN
SET @varSql = CONCAT(' WHERE', @varSql);
SET @Sql = CONCAT(@varSqlPrefix, @varSql);
ELSE
SET @Sql = @varSqlPrefix;
END IF;

PREPARE STMT FROM @Sql;
EXECUTE STMT;

END

Env: Java + Mysql
I have found that the input parameters were not null, [a single space]. Then I changed the Java code as:

if(parameter == "") {
cs.setNull(position, java.sql.Types.VARCHAR);
} else {
cs.setString(position, parameter);
}

The setNull() method will set a null value for the IN parameter from Java -> MySql, otherwise, you will get a single space in the SP.

However, the code above is good enough, because code is duplicated. I'm looking for a better way to handle this. E.g. All Stored Procedures {Call} One Single Function to generate dynamic Sql.

Any comments?

Thanks,
Ming