Issue with building dynamic SQL statement based on parameters passed to Stored procedure.

[Ravi G]

How do i build dynamic SQL in Stored procedures based on no.of valid arguments passed?

Thanks for the help in advance guys. Here is my situation.

I need to build my SQL Query in Procedure based on the NOT NULL values of the arguments. Let us say my stored procedures takes three values City, State and Department number.
User expected to pass all three values then it makes it simple...as i just used the parameters directly in SQL query.
However Users may pass null values for some or all these values. So based on valid no.of of parameters i need to dynamically build where clause with valid column values passed to the stored procedure.

Example 1: User passed only State then my where clause should have only state=p_state. IF user also passed City then it should have state=p_state and city=p_city etc....,

Example 2: If user passes null for all thre parameters then i need to set and use default values

Here is what i have written and it is failing with syntax error.

DELIMITER\\

CREATE DEFINER=`abcde`@`%` PROCEDURE `getemployeelist`( IN p_city varchar(20), IN p_state varchar(5), IN p_dno int)

BEGIN

IF p_city=null then
SET @city="DALLAS";
END IF;

IF p_city=null then
SET @state="TX";
END IF;

SET @whereclause=CONCAT('DEPT.city=',@city,'DEPT.state=',@state);
SET @stmt= 'SELECT EMP.* FROM EMPLOYEE EMP INNER JOIN DEPT D ON EMP.DNO=DEPT.DNO WHERE';
SET @stmt1=CONCAT(@stmt,' ',@whereclause);

PREPARE stmt2 FROM @stmt1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

END\\

DELIMITER ;