declare @proj_id_in	int,
   @order_by_in	char(30)

set @proj_id_in = NULL
set @order_by_in = 'proj_lead'

SELECT a.proj_id, a.proj_name, a.proj_desc, a.proj_priority, convert(char(10),a.request_received,101) as 'request_received', convert(char(10),a.due_date,101) as 'due_date', 
   convert(char(10),a.date_completed,101) as 'date_completed',
   b.team_desc, c.emp_lname + ', ' + c.emp_fname as 'proj_lead', a.client_contact, d.status_desc
from cts_proj a
   JOIN cts_team b on a.team_code = b.team_code
   JOIN cts_emp c on a.proj_lead = c.emp_id
   JOIN cts_status d on a.status_code = d.status_code
WHERE (@proj_id_in IS NULL OR a.proj_id = @proj_id_in)
order by 
 CASE rtrim(@order_by_in)
		WHEN 'proj_id' THEN a.proj_id
		WHEN 'proj_name' THEN a.proj_name
		WHEN 'proj_priority' THEN a.proj_priority
		WHEN 'requested_date' THEN a.request_received
		WHEN 'due_date' THEN a.due_date
		WHEN 'completion_date' THEN a.date_completed
		WHEN 'proj_lead' THEN a.proj_name
		WHEN 'client_contact' THEN client_contact
		WHEN 'status_desc' THEN status_desc
	 END

 ORDER BY
    CASE rtrim(@order_by_in)
		WHEN 'proj_id' THEN convert(char(10),a.proj_id)
		WHEN 'proj_name' THEN a.proj_name
		WHEN 'proj_priority' THEN convert(char(10),a.proj_priority)
		WHEN 'requested_date' THEN convert(char(10),a.request_received,101)
		WHEN 'due_date' THEN convert(char(10),a.due_date,101)
		WHEN 'completion_date' THEN convert(char(10),a.date_completed,101)
		WHEN 'proj_lead' THEN c.emp_lname + c.emp_fname
		WHEN 'client_contact' THEN a.client_contact
		WHEN 'status_desc' THEN d.status_desc
 

WHEN 'due_date' THEN LTRIM(STR(DATEPART(yyyy,a.due_date))) +
RIGHT('00'+LTRIM(STR(DATEPART(mm,a.due_date))),2) +
RIGHT('00'+LTRIM(STR(DATEPART(dd,a.due_date))),2)