Re: Dynamic Sql Statement in SP

[Roland Bouman]

Ming,

I figured you are getting some kind of error saying that the statement could not be prepared, right? (Your post didn't say so exactly, I'm guessing)

I think your code is not working because of this:

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

in the top, you do this:

SET @varSql = ' ';

Suppose all of your input parameters are NULL, then @varSql would no have been changed. So the value of @varSql would still be '', which is not the same as NULL. So, it should read:

IF @varSql != '' THEN
....
END IF;

To be sure, just write a

SELECT @fullSQL
;

before the prepare so you can see what you've actually generated. run the proc in the comman d line tool, and copy the statement from the output. Then, test is you can run the generated query directly. If that succeeds, you should be able to prepare and execute.