MySQL Forums
Forum List  »  Stored Procedures

Re: Dynamic Sql Statement in SP
Posted by: Roland Bouman
Date: January 09, 2006 02:10PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2924
January 09, 2006 06:22AM
1584
January 09, 2006 08:06AM
Re: Dynamic Sql Statement in SP
1310
January 09, 2006 02:10PM
1889
January 09, 2006 08:02PM
1765
January 10, 2006 12:24AM
1581
January 10, 2006 03:07AM
1784
January 10, 2006 06:51AM
1490
January 10, 2006 01:49PM
1842
January 10, 2006 06:35PM
1640
January 10, 2006 07:22PM
1724
January 10, 2006 10:33PM
1735
January 11, 2006 03:10AM
2291
January 11, 2006 04:27AM
1726
January 11, 2006 06:54AM
1579
January 11, 2006 03:34PM
1753
January 11, 2006 03:56PM
1747
January 12, 2006 03:20AM
1533
January 12, 2006 04:44AM
1518
January 11, 2006 09:08PM
1754
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.