how to pass a parameter for order by clause in sql server?

Question

0.00/5 (No votes)

See more:

hi sir,

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:

SQL

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

and i am facing this error

SQL

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 sir, please give a solution

Thanks

Posted 27-Aug-10 20:48pm

ajay.raju531

Updated 27-Aug-10 20:52pm

thatraja

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thatraja · Answer 1 · 2010-08-27T20:51:00

Solution 1

You can use sp_executesql for your issue, build the query based on parameter then exec with sp_executesql. Try this link Why not to use sp_executesql?[^]

Posted 27-Aug-10 20:51pm

thatraja

v2

senguptaamlan · Answer 2 · 2010-08-27T22:26:00

@thatraja U r right....to help a bit more I tried to write the procedure for him....

SQL

CREATE PROC sp_Sortdata
@OrderByColumnName nvarchar(15)
AS
    DECLARE @SQLStatement nvarchar(max)
    SET @SQLStatement = N'select * from emp order by '+@OrderByColumn
    EXEC sp_executesql @statement = @SQLStatement

Now execute the created stored proc by using

exec sp_SortData @OrderByColumnName='ColumnName'

Here ColumnName is the name of the column by which you want to do the sorting

Simple isn't :cool:

If you like my answer please, vote it or mark it as answer.

hiren soni · Answer 3 · 2010-08-30T06:44:00

Solution 3

SQL

DECLARE @STR VARCHAR(1000)

SET @STR = 'selecet * from emp order by '+  @Orderbyclause
EXECUTE(@STR)

Posted 30-Aug-10 6:44am

hiren soni