I’ll refer you to the answer I posted over on https://itknowledgeexchange.techtarget.com/itanswers/stored-procedure-problem.

In order to have a dynamic order by you have two options.

1. Use dynamic SQL.
2. Use a branching storage procedure.

Option 1:
<pre>CREATE PROCEDURE MyProc
@OrderBy NVARCHAR(30)
AS
DECLARE @SQL NVARCHAR(4000)
SET @SQL = N“Select * From Tbl_Name Where Name LIKE ‘%xyz%’ ” + @OrderBy
exec (@SQL)
GO</pre>

Option 2:
<pre>CREATE PROCEDURE MyProc
@OrderBy INT
AS
IF @OrderBy = 1
SELECT * FROM Tbl_Name WHERE Name LIKE ‘%xyz%’ ORDER BY publication DESC
IF @OrderBy = 2
SELECT * FROM Tbl_Name WHERE Name LIKE ‘%xyz%’ Order by BookName ASC
IF @OrderBy = 3
SELECT * FROM Tbl_Name WHERE Name LIKE ‘%xyz%’ Order by AuthorName ASC
IF @OrderBy = 4
SELECT * FROM Tbl_Name WHERE Name LIKE ‘%xyz%’ Order by publication ASC
GO</pre>

The problem with dynamic SQL is there are security risks with using it.
The problem with the branching code is that SQL can’t do much with caching the execution plan (especially in SQL 2000 and below).