?
Solved

T-SQL @SortOrder incorrect syntax

Posted on 2011-10-18
6
Medium Priority
?
245 Views
Last Modified: 2012-05-12
what is the problem with this query? The Error message: "Incorrect syntax near '@SortOrder'."


declare
      @SortColumn varchar(100)
      , @SortOrder varchar(5)

set @SortColumn = 'Title'
set @SortOrder = 'ASC'

select
      *
from
      Projects
Order By
      @SortColumn @SortOrder
Untitled.jpg
0
Comment
Question by:conrad2010
  • 4
  • 2
6 Comments
 
LVL 7

Expert Comment

by:mmr159
ID: 36989380
You cannot sort by variable.
0
 

Author Comment

by:conrad2010
ID: 36989393
how can I add the ASC or DESC value as a parameter?
0
 
LVL 7

Accepted Solution

by:
mmr159 earned 2000 total points
ID: 36989400
You either have to

SELECT *
FROM Projects
ORDER BY Title ASC

OR

DECLARE @s VARCHAR(8000),
    @sort_column VARCHAR(100,
    @sort_order VARCHAR(5)

SET @sort_column = 'Title'
SET @sort_order = 'ASC'

SET @s =
' SELECT * FROM Projects ORDER BY ' + @sort_column + ' ' + @sort_order

EXEC @s
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Closing Comment

by:conrad2010
ID: 36989413
Thanks!
0
 
LVL 7

Expert Comment

by:mmr159
ID: 36989415
The second option in my previous post is called dynamic SQL.  You will need that in order to do what you are trying to do.  It is not recommended.  Typically, a default sort is done with the query, then the front end is allowed to sort by column asc/desc.
0
 
LVL 7

Expert Comment

by:mmr159
ID: 36989419
No prob.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question