Doing this type of thing in the order by clause.. now that's the way forward!

If you have 20 lines of sql in an sp and have to order the results in 5 different ways, say by date, firstname, surname, personId, email... that's when you can really cut down on your code in your stored procedure, and avoid 'dynamic queries'.

I don't give any permissions on tables to client applications.. maybe I am harsh, but I like the added layer.

CREATE PROCEDURE usp_getMyData

@orderBy varchar(20)

AS

SELECT personId, birthDate, firstname, surname, email

FROM dbo.persons

ORDER BY

CASE @orderBy

WHEN 'personId' THEN personId

END

DESC,

CASE @orderBY

WHEN 'firstname' THEN firstname

WHEN 'surname' THEN surname

WHEN 'email'THEN email

END

DESC,

CASE

WHEN @orderBy IS NULL THEN birthDate

END

DESC

GO