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

LVL 6
J NUnicorn wranglerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J NUnicorn wranglerAuthor Commented:
thats what i ended up doing thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.