CODEWITHSUNDEEP
c#sqlsql-server
beginnerlaravelvue
beginnerlaravelvue

Reputation: 157

How to insert asc/desc in addParameter for sorting in SQL query

I have this code

dBCommand.AddParameter("@SORT", "asc");

Below is my query.

 SELECT TOP(1) RECEIPTID [RECEIPTID], TRANSTIME [TRANSTIME] FROM RetailTransactionTable
    WHERE TRANSDATE = @TRANSDATE 
    AND RECEIPTID != ''
    AND STORE = @STORE
    AND TERMINAL = @TERMINAL
    ORDER BY TRANSTIME @SORT

How can I place an asc and desc in AddParameter? Because it throws an error I guess because it is not in the right format?

Upvotes: 1

Views: 791

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 82020

If by chance TRANSTIME has a datatype of datetime you can convert to a float and then apply a factor.

HOWEVER, I suspect a conditional approach would be more performant.

  SELECT TOP(1) RECEIPTID [RECEIPTID], TRANSTIME [TRANSTIME] FROM RetailTransactionTable
    WHERE TRANSDATE = @TRANSDATE 
    AND RECEIPTID != ''
    AND STORE = @STORE
    AND TERMINAL = @TERMINAL
    ORDER BY convert(float,TRANSTIME) * IIF(@Sort='asc',1,-1)

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 8000

It is unfortunately not possible. You can set if-else structure to handle this issue like:

if(orderParameter == "asc")
{
 SELECT TOP(1) RECEIPTID [RECEIPTID], TRANSTIME [TRANSTIME] FROM RetailTransactionTable
 WHERE TRANSDATE = @TRANSDATE 
 AND RECEIPTID != ''
 AND STORE = @STORE
 AND TERMINAL = @TERMINAL
 ORDER BY TRANSTIME ASC
}
else
{
 SELECT TOP(1) RECEIPTID [RECEIPTID], TRANSTIME [TRANSTIME] FROM RetailTransactionTable
 WHERE TRANSDATE = @TRANSDATE 
 AND RECEIPTID != ''
 AND STORE = @STORE
 AND TERMINAL = @TERMINAL
 ORDER BY TRANSTIME DESC
}

Or you can use String.Format(..) to set your query like

string orderParameter = "asc";
string sql = String.Format("SELECT TOP 1 RECEIPTID [RECEIPTID], TRANSTIME [TRANSTIME] 
               FROM RetailTransactionTable
               WHERE TRANSDATE = @TRANSDATE 
               AND RECEIPTID != ''
               AND STORE = @STORE
               AND TERMINAL = @TERMINAL
               ORDER BY TRANSTIME {0}",orderParameter);

Upvotes: 1

user1666620
user1666620

Reputation: 4808

Within the query, you have an if statement which orders the results based on the @sort parameter value.

something like:

if @sort = 'ascending'
    select ... order by transtime asc
else
    select ... order by transtime desc

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1064324

The sort direction is part of the query itself and cannot be parameterized. You would need to either construct the query to embed ASC or DESC into the SQL (essentially string.Format or concatenation, but just of the ASC/DESC part - not of the input values), or have 2 different completed queries that you issue.

Upvotes: 1

Related Questions