Editing MSSQL Cookbook (section)

==Dynamic sorting in a stored procedure==

'''Goal:''' Pass an argument to a stored procedure that will control how the recordset is sorted.

<syntaxhighlight lang="sql">
SELECT 
	t.row1
	, t.row2
	, t.row3
	, ROW_NUMBER() OVER 
	(
		/* sort expressions */
		ORDER BY 
		CASE WHEN @vcSortType = 'row1' AND (NULLIF(@vcSortOrder,'') IS NULL OR @vcSortOrder = 'ASC') THEN t.row1 END ASC,
		CASE WHEN @vcSortType = 'row1' AND @vcSortOrder = 'DESC' THEN t.row1 END DESC,
		CASE WHEN @vcSortType = 'row2' AND (NULLIF(@vcSortOrder,'') IS NULL OR @vcSortOrder = 'ASC') THEN t.row2 END ASC,
		CASE WHEN @vcSortType = 'row2' AND @vcSortOrder = 'DESC' THEN t.row2 END DESC,
		CASE WHEN @vcSortType = 'row3' AND (NULLIF(@vcSortOrder, '') IS NULL OR @vcSortOrder = 'ASC') THEN t.row3 END ASC,
		CASE WHEN @vcSortType = 'row3' AND @vcSortOrder = 'DESC' THEN t.row3 END DESC,

		/* etc... */

		t.row1 ASC, t.row2 ASC /* <<< this is the default sort order */
	) rowNumber
FROM [dbo].[MyTable] t WITH(NOLOCK)
ORDER BY rowNumber
</syntaxhighlight>