Myujth

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?

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

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.

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);

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)