how to write stored procedure for order by clause?

Question

hi,

in my front end application i take one dropdown list and datagrid. that dropdownlist purpose is sorting data. i take Ename, Esal, Dept three items to the dropdownlist.

if user select any one item from dropdownlist, that time data diplayed sorting order of selected item.

i am write stored procedure like:

create procedure sp_Sortdata
(
@Orderbyclause varchar(50)
)
as
begin
selecet * from emp order by @Orderbyclause
end

and i am facing this error

 

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

please correct my query and please give a solution

Thanks

Answer 1

hi,

assume that you have the columns emp_id and emp_name in your emp-table you can use the following procedure:

create procedure sp_Sortdata
(
    @Orderbyclause varchar(50)
)
as
select
    *
from
    emp e
order by   
    case when @Orderbyclause = 'emp_id' then e.emp_id end asc
    ,case when @Orderbyclause = 'emp_name' then e.emp_name end asc
   
if you execute exec sp_Sortdata 'emp_id' the procedure returns the emp-table ordered by emp_id

i hope this helps

Marcel Hertel