• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 46
  • Last Modified:

Dynamic ordering of columns in stored procedure

how to dynamically select columns in stored procedure

//There are six variables
  • var_order -> toggle between first name and last name first. The columns are generated from php
  • var_limit  -> limit on sql query
  • var_user -> user id
  • var_status -> "active"
  • var_activity -> activity id
  • var_team ->  team id

//var_order is either passed as a string for either p.first_name," ", p.last_name or p.last_name, ", ", p.first_name

DELIMITER $$

CREATE PROCEDURE brings_schedule( IN var_order VARCHAR(255), IN var_limit INT, IN var_user INT, IN var_status VARCHAR(1), IN var_activity INT, IN var_team INT )

BEGIN
	    
	SELECT 
		s.id, s.item, s.description, 
		CONCAT(var_order) AS user, p.id AS userId, p.unique_name,
		r.teamId, 
		q.uniqueName
	FROM brings AS s
	JOIN users AS p ON p.id = s.userId AND p.status != var_user
	JOIN `teams-roster` AS r ON r.teamId = var_team AND r.userId = var_user AND r.status = var_status
	JOIN teams AS q ON q.id = r.teamId
	WHERE s.status = var_startus AND s.teamId = var_team AND s.activityId = var_activity
	GROUP BY s.id
	ORDER by p.first_name
    LIMIT var_limit;

END;

$$

DELIMITER ;

Open in new window

0
J N
Asked:
J N
1 Solution
 
PortletPaulfreelancerCommented:
Use 2 case expressions in the order by clause based on the actual value you send through var_order e.g.

      ORDER by
              case when var_order = 'p.first_name," ", p.last_name' then p.first_name else last_name end
            , case when var_order = 'p.first_name," ", p.last_name' then p.last_name else first_name end

so,
if the request is first_name you get an order on first_name then last_name,
otherwise you get an order by on last_name then first_name
0
 
J NUnicorn wranglerAuthor Commented:
thats what i ended up doing thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now