Ryan J.com

Well, today I ran into some old code where I needed to add a dynamic sort to an ASP page. Here was the solution, which required an adaptation to the stored procedure pulling the data... after the initial select and where clause this is placed after the ORDER BY:

        .... create procedure

	....

	@orderby VARCHAR (20) = NULL

	...

	SELECT column1, column2

	FROM table

	ORDER BY

	CASE WHEN @orderby='column1 ASC' THEN column1 END ASC, 

        CASE WHEN @orderby='column1 DESC' THEN column1 END DESC,

        CASE WHEN @orderby='column2 ASC' THEN column2 END ASC, 

        CASE WHEN @orderby='column2 DESC' THEN column2  END DESC,

        CASE WHEN @orderby='' THEN column1 END ASC

I'm passing a potential null parameter into the stored procedure containing a string from the application. So the parameter has "column1 ASC" if I want to sort by column1 in ascending order. Pretty easy... just wanting to make a quick note it.