CREATE PROCEDURE gettheesorted (@sortorder char(12) = 'ANY', @sortdirection char(2) = 'UP')

AS

SET NOCOUNT ON

IF @sortdirection = 'UP'   ---MAIN IF

BEGIN                      ---main if

SELECT X.fbompart, X.fbomdesc, Z.fvendno, X.jsource, Z.company, X.fpono, X.factqty, X.fbominum, Y.jvar  FROM (tables X,Y,Z in some form)

ORDER BY

	case @sortorder when 'part' then X.fbompart else null end asc,
	case @sortorder when 'descrip' then X.fbomdesc else null end asc,
	case @sortorder when 'vendor' then Z.fvendno else null end asc,
	case @sortorder when 'source' then X.jsource else null end asc,
	case @sortorder when 'company' then Z.company else null end asc,		
	case @sortorder when 'pono' then X.fpono else null end asc,
	case @sortorder when 'actqty' then X.factqty else null end asc,
	case @sortorder when 'bomitemno' then X.fbominum else null end asc,	
	case @sortorder when 'variance' then Y.jvar else null end asc 

END       ---main if

ELSE      ---MAIN IF  This is implimented simply to allow two dynamic sort directions without using dynamic SQL

BEGIN     ---main if

SELECT X.fbompart, X.fbomdesc, Z.fvendno, X.jsource, Z.company, X.fpono, X.factqty, X.fbominum, Y.jvar  FROM (tables X,Y,Z in some form)

ORDER BY
	
	case @sortorder when 'part' then X.fbompart else null end desc,
	case @sortorder when 'descrip' then X.fbomdesc else null end desc,
	case @sortorder when 'vendor' then Z.fvendno else null end desc,
	case @sortorder when 'source' then X.jsource else null end desc,
	case @sortorder when 'company' then Z.company else null end desc,		
	case @sortorder when 'pono' then X.fpono else null end desc,
	case @sortorder when 'actqty' then X.factqty else null end desc,
	case @sortorder when 'bomitemno' then X.fbominum else null end desc,	
	case @sortorder when 'variance' then Y.jvar else null end desc 

END       ---main if


 

quote:

---

And in that case, you can explicitly CAST the sort column to a character type, but you'd have to make sure of leading zeros or spaces for numerics, and use ISO date format for dates.

Another option that eliminates the datatype problem:

SELECT *, CASE @sortColumn
WHEN 'myDate' THEN myDate
WHEN 'FirstName' THEN FirstName
WHEN 'LastName' THEN LastName
ELSE ID END AS SortCol
FROM myTable
ORDER BY SortCol

The only downsides are that it adds an extra column to your output, and you need multiple expressions for multi-column sorts. But it's a little easier to maintain since you simply add extra WHEN clauses for each sort expression without having to convert anything, and you never have to alter the ORDER BY clause.

---