Help me with SP OrderBy

I have a SP that accepts parameters, but the problem is that it is not ORDERING Data as required, It is returning ORDER BY NULL instead of order by my parameter "NAME "here is the output from SQL 2000 DB

'ELP B4 I jump out of LondonBridge

**********************************
SELECT * FROM #TempTable WHERE ID > 0 AND ID < 6 AND EmployerID = 54 AND Job_no = 40

ORDER BY NULL
****************************************

SELECT @.MYSTATEMENT = ' SELECT * FROM #TempTable
WHERE
ID > '+ convert(varchar(20),@.FirstRec) +'
AND
ID < '+convert(varchar(20),@.LastRec) +'
AND EmployerID = '+ convert(varchar(20),@.EmployerID)+'
AND Job_no = '+convert(varchar(20),@.Job_no)+'
ORDER BY ' +
CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc'
THEN 'name'
ELSE 'NULL'
END
+
CASE WHEN @.WhichColumn='name' AND @.sortby='DESC'
THEN ' desc'
ELSE ''
END
EXEC (@.myStatement)

try :

ORDER BY ' +
CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc' THEN 'name ASC'
 WHEN @.WhichColumn = 'name' AND @.sortby = 'desc' THEN 'name desc'
ELSE 'NULL'
END

also do a print @.myStatement before you execute the statement to see how its building up.

hth|||Hi this is the output, it is still showing null

SELECT * FROM #TempTable

WHERE
ID > 0
AND
ID < 6
AND EmployerID = 54
AND Job_no = 40

ORDER BY NULL

(5 row(s) affected)|||what value are you passing to the parameter @.WhichColumn ?|||I have done a print on @.whichColumn, it is giving me the right value which is 'NAME'
So the problem must be from the case statement, but I can't figure it out

Thanks|||

declare @.MYSTATEMENT varchar(500), @.WhichColumn varchar(100), @.sortby varchar(100)
set @.sortby = 'desc'
set @.WhichColumn = 'name'
SELECT @.MYSTATEMENT = ' SELECT * FROM #TempTable WHERE ID > 6 AND ID < 10 ORDER BY ' +
CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc' THEN 'name ASC'
 WHEN @.WhichColumn = 'name' AND @.sortby = 'desc' THEN 'name desc'
ELSE 'NULL'
END
print @.mystatement

i just did this in my QA and it worked fine for me. so double check your code.

hth