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.