Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: how to dynamically build select statements in a stored procedure?

    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:

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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 @.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •