How to Add order by as a parameter in stored procedure?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hey Guys,

i have a stored procedure which selects from the data base,

i want to add order by column as a parameter

and i want to add the sort type as a parameter too

EX: select * from Students order by @Column_Name @Sort_Type

@Column_Name is the sortable column
@Sort_Type is ASC or DESC

Any Ideas

Posted 9-Apr-13 4:39am

_ProgProg_

Updated 9-Apr-13 4:40am

v2

Add a Solution

Comments

govardhan4u 9-Apr-13 10:45am

check this URL

http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

_ProgProg_ 9-Apr-13 10:48am

Is there is another way except the dynamic sql statment?

José Amílcar Casimiro 9-Apr-13 10:52am

It is really necessary order data in sql statement? Can you order it in a frontend application?

govardhan4u 9-Apr-13 10:58am

If you are calling it from a .Net code then you can generate the sql statement and call its execute comman

1 solution

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)

Maciej Los · Accepted Answer · 2013-04-09T05:33:00

Try this:

SQL

CREATE PROCEDURE MyProc
     @Column_Name VARCHAR(30),
     @Sort_Type VARCHAR(10)
AS
BEGIN
    DECLARE @sql VARCHAR(2000)

    SET @sql = 'SELECT * ' +
               'FROM Students ' + 
               'ORDER BY ' + @Column_Name + ' ' + @Sort_Type
    EXEC (@sql)

END

As govardhan4u[^] wrote, see this article: Building Dynamic SQL In a Stored Procedure[^]