Construct Dynamic Query Inside MySQL Stored Procedure

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. 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s