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
Marcel HertelPosted Aug 28, 2010, 5:55 AM
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