Reputation: 157
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
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
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
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
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