how to dynamically build select statements in a stored procedure?

ozzii · #1 (**permalink**) 03-13-09, 15:09

Is it possible to dynamically build a select statements in a stored procedure?

I have the following procedure:

Code:

CREATE PROCEDURE count_books(IN inSearchString TEXT)
BEGIN
      PREPARE statement FROM
	 "SELECT COUNT(DISTINCT books.book_id) 
	       FROM tbl_books as books
         INNER JOIN tbl_book_search AS bk_search 
                ON bk_search.book_id = books.book_id
         WHERE  (books.book_status = 'Approved')
         IF (? <> "") THEN
                 AND MATCH (bk_search.book_title, bk_search.book_desc)
                     AGAINST (?)
         END IF
         ";

     SET @p1 = inSearchString;
     
     EXECUTE statement USING @p1, @p1;
END

but keep getting the following error message:

mike_bike_kite · #2 (**permalink**) 03-13-09, 16:35

Quote:

Is it possible to dynamically build a select statements in a stored procedure?

You can't build select statement dynamically in MySQL stored procs. This is likely to change in future versions. Why are you using a stored proc to do a simple query? Far better to just build the query in the calling program and then run the select from there. Your variable names also look like Sybase variables - MySQL does not require the @.

Internet Services

Web Development

Digital Marketing

Consumer Tech