MySQL Forums
Forum List  »  Stored Procedures

dyna query problem
Posted by: Raj Chak
Date: August 06, 2012 12:53AM

Hi,
I am trying to run a dynamic query in my SP.
When I run it, it says...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

A sample call looks like...
call `b2b`.`qb_sproc_search_item_details1`('test', null, null, 0, 50, @a);
The problem is in the where clause where I do the null check.
Please help.

CREATE DEFINER=`root`@`localhost` PROCEDURE  `b2b`.`qb_sproc_search_item_details1`(

in_item_name VARCHAR(100),
in_advertiser VARCHAR(100),
in_display_name VARCHAR(100),
in_offset int(11),
in_limit int(11),
OUT out_total int(11)

)
BEGIN

set @dyna_query = concat('SELECT SQL_CALC_FOUND_ROWS i.id, i.name, a.name advertiser_name, concat(u.first_name, \' \', u.last_name) created_by, i.created_on
                            FROM qb_items i
                            JOIN qb_advertisers a ON(i.advertiser_id = a.id)
                            JOIN qb_users u ON(i.created_by = u.id)
                            WHERE (in_item_name IS NULL OR i.name LIKE \'%', in_item_name, '%\')
                            AND (in_advertiser IS NULL OR i.advertiser_id IN (SELECT id FROM qb_advertisers WHERE name LIKE \'%', in_advertiser, '%\'))
                            AND (in_display_name IS NULL OR i.display_name LIKE \'%', in_display_name, '%\')
                            ORDER BY id DESC LIMIT ', in_limit, ' OFFSET ', in_offset);

prepare statement from @dyna_query;
execute statement;
deallocate prepare statement;

SET out_total = found_rows();

END $$



Edited 1 time(s). Last edit at 08/06/2012 01:58AM by Raj Chak.

Options: ReplyQuote


Subject
Views
Written By
Posted
dyna query problem
2531
August 06, 2012 12:53AM
963
August 08, 2012 03:56AM
801
August 09, 2012 12:39AM
970
August 09, 2012 02:22AM
832
August 09, 2012 02:34AM


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.