ну или же можно как-то так

declare @OrdFld varchar(99)
select @OrdFld='name'

select * from sysobjects
order by
case when @OrdFld='name' then name else '' end,
case when @OrdFld='id' then id else 0 end,
case when @OrdFld='type' then tyoe else '' end
и т.д.

2Glory
тема EXECUTE, T-SQL Reference

Permissions
EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.

А какие проблеммы?

declare @OrdFld varchar(99), @desc int
select @OrdFld='name', @desc =1

select * from sysobjects
order by
case when @OrdFld='name' and @desc=0 then name else '' end,
case when @OrdFld='id' and @desc=0 then id else 0 end,
case when @OrdFld='type' and @desc=0 then type else '' end,
case when @OrdFld='name' and @desc=1 then name else '' end desc,
case when @OrdFld='id' and @desc=1 then id else 0 end desc,
case when @OrdFld='type' and @desc=1 then type else '' end desc

Мне казалось что это очевидно...