MySQL Forums
Forum List  »  InnoDB

Re: Stored Procedure, IF in the SQL select Where Clause?
Posted by: William Chiquito
Date: June 29, 2008 09:16AM

Hi Brett,

Option 1 is:
DELIMITER $$

DROP PROCEDURE IF EXISTS `sptest`$$

CREATE PROCEDURE `sptest`(datestart DATETIME)
BEGIN
	SELECT * FROM website WHERE owner = 1 AND CASE
						WHEN datestart IS NOT NULL THEN tracking.tdate > DATE(datestart)
						WHEN datestart IS NULL THEN 0 = 0
						END;
END$$

DELIMITER ;
Option 2 is:
DELIMITER $$

DROP PROCEDURE IF EXISTS `sptest`$$

CREATE PROCEDURE `sptest`(datestart DATETIME)
BEGIN
	SET @SQLstmt = CONCAT('SELECT * FROM website WHERE owner = 1',
                                        IF(datestart IS NOT NULL, ' AND tracking.tdate > DATE(datestart)', ''));
	PREPARE SQLbase FROM @SQLstmt;
	EXECUTE SQLbase;
	DEALLOCATE PREPARE SQLbase;
END$$

DELIMITER ;



Edited 2 time(s). Last edit at 06/29/2008 09:18AM by William Chiquito.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored Procedure, IF in the SQL select Where Clause?
6818
June 29, 2008 09:16AM


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.