Construct Dynamic Query Inside MySQL Stored Procedure
by Prabhat Rai

Sometimes we need to construct a query inside a stored procedure. Lets say we have fields like day1, day2, day3…, and we need to fetch day1, for some other case we need to fetch for day2 and so on. Why write different procedures just write a single SP construct the query by passing the day number and then execute the query. Lets see this with an example :

BEGIN

    DECLARE FIELD_FETCH TEXT;

     SET FIELD_FETCH = CONCAT('day',v_day);

    SET @sql = CONCAT('SELECT id,', FIELD_FETCH, ' AS current_day FROM xxx');

    PREPARE stmt FROM @sql;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

END

Explanation :

The first 2 line we declared a field FIELD_FETCH and assigned it with the value “dayN” where N is the parameter v_day passed to SP

Then we construct query using CONCAT function, Then we Prepare SQL statement from the created query, The next line executes the query, then we deallocate the SQL statement.

Lets say we pass 1 as v_day, then our SQL query will be,

SELECT id,day1 As current_day FROM xxx;

If we pass 2 then,

SELECT id,day2 As current_day FROM xxx;

We can use this approach to save our time. Happy Coding. 🙂