How to use case in sql select

[Tony Johansson]

Hello!

The passed orderBy parameter string can consist of one of these 8
possibilities
The code is at the end.

TicketID ASC
TicketI DESC
Priority ASC
Priority DESC
CreatedDate ASC
CreatedDate DESC
Owner ASC
Owner DESC

I want to sort in the way that the given parameter orderBy is specified.
It would be easy to use string.format but I hope that I can avoid that
because of sql injection

I hope somebody know how to fix this in an easy way without using
string.format.
I tried below but I get error probably of not understanding this fully.
It must be an easier way to use case then I have done below.
It seems like a lot of writing whan I have the sort expression ready to be
used.

public DataSet GetTicketDetail(ListItem owner, string orderBy)
{
SqlCommand cmd;
if (owner.Text == "*") // All tickets that has an owner
{
string query = "SELECT Tickets.TicketID, Tickets.HeadLine,
Tickets.Description, " +
"Tickets.Priority, Tickets.CreatedTicket,
Users.Owner, Tickets.Complete, TicketType.Name " +
"FROM Tickets " +
"JOIN Users ON " +
"Users.UserID = Tickets.UserID " +
"JOIN TicketType ON " +
"TicketType.TicketTypeID = Tickets.TicketTypeID
" +
"where Tickets.UserID != @UserID " +
"ORDER BY " +
"CASE WHEN @orderBy = 'TicketID ASC' " +
"THEN TicketID END ASC, " +
"CASE WHEN @orderBy = 'TicketID DESC' " +
"THEN TicketID END DESC, " +
"CASE WHEN @orderBy = 'Priority ASC' " +
"THEN Priority END ASC, " +
"CASE WHEN @orderBy = 'Priority DESC' " +
"THEN Priority END DESC, " +
"CASE WHEN @orderBy = 'CreatedDate ASC' " +
"THEN CreatedDate END ASC, " +
"CASE WHEN @orderBy= 'CreatedDate DESC' " +
"THEN CreatedDate END DESC, " +
"CASE WHEN @orderBy= 'Owner ASC' " +
"THEN Owner END ASC, " +
"CASE WHEN @orderBy= 'Owner DESC' " +
"THEN Owner END DESC " +
"SELECT
TicketDetail.TicketDetailID,TicketDetail.TicketID, TicketDetail.Action, " +
"Users.Owner, TicketDetail.CreatedDate,
TicketDetail.UserID,TicketDetail.Status " +
"FROM [TicketDetail] " +
"JOIN Users ON " +
"Users.UserID = TicketDetail.UserID";

cmd = new SqlCommand(query);
cmd.Parameters.AddWithValue("@UserID",
GetIDForNonAssignedTickets());
return FillDataSet(cmd, "Tickets");
}
}
//Tony

 

[Jeff Johnson]

The passed orderBy parameter string can consist of one of these 8
possibilities
The code is at the end.

TicketID ASC
TicketI DESC
Priority ASC
Priority DESC
CreatedDate ASC
CreatedDate DESC
Owner ASC
Owner DESC

I want to sort in the way that the given parameter orderBy is specified.
It would be easy to use string.format but I hope that I can avoid that
because of sql injection

You're going to have to. The ORDER BY clause cannot be dynamic, which is
what you're trying to do. Sometimes you have to bite the bullet and build an
SQL string; this is one of those times.

 

[bradbury9]

El lunes, 9 de julio de 2012 18:48:43 UTC+2, Tony Johansson escribió:

Hello!

The passed orderBy parameter string can consist of one of these 8
possibilities
The code is at the end.

TicketID ASC
TicketI DESC
Priority ASC
Priority DESC
CreatedDate ASC
CreatedDate DESC
Owner ASC
Owner DESC

I want to sort in the way that the given parameter orderBy is specified.
It would be easy to use string.format but I hope that I can avoid that
because of sql injection

I hope somebody know how to fix this in an easy way without using
string.format.
I tried below but I get error probably of not understanding this fully.
It must be an easier way to use case then I have done below.
It seems like a lot of writing whan I have the sort expression ready to be
used.

public DataSet GetTicketDetail(ListItem owner, string orderBy)
{
SqlCommand cmd;
if (owner.Text == "*&quot // All tickets that has an owner
{
string query = "SELECT Tickets.TicketID, Tickets.HeadLine,
Tickets.Description, " +
"Tickets.Priority, Tickets.CreatedTicket,
Users.Owner, Tickets.Complete, TicketType.Name " +
"FROM Tickets " +
"JOIN Users ON " +
"Users.UserID = Tickets.UserID " +
"JOIN TicketType ON " +
"TicketType.TicketTypeID = Tickets.TicketTypeID
" +
"where Tickets.UserID != @UserID " +
"ORDER BY " +
"CASE WHEN @orderBy = 'TicketID ASC' " +
"THEN TicketID END ASC, " +
"CASE WHEN @orderBy = 'TicketID DESC' " +
"THEN TicketID END DESC, " +
"CASE WHEN @orderBy = 'Priority ASC' " +
"THEN Priority END ASC, " +
"CASE WHEN @orderBy = 'Priority DESC' " +
"THEN Priority END DESC, " +
"CASE WHEN @orderBy = 'CreatedDateASC' " +
"THEN CreatedDate END ASC, " +
"CASE WHEN @orderBy= 'CreatedDate DESC' " +
"THEN CreatedDate END DESC, " +
"CASE WHEN @orderBy= 'Owner ASC' " +
"THEN Owner END ASC, " +
"CASE WHEN @orderBy= 'Owner DESC' " +
"THEN Owner END DESC " +
"SELECT
TicketDetail.TicketDetailID,TicketDetail.TicketID, TicketDetail.Action, " +
"Users.Owner, TicketDetail.CreatedDate,
TicketDetail.UserID,TicketDetail.Status " +
"FROM [TicketDetail] " +
"JOIN Users ON " +
"Users.UserID = TicketDetail.UserID";

cmd = new SqlCommand(query);
cmd.Parameters.AddWithValue("@UserID",
GetIDForNonAssignedTickets());
return FillDataSet(cmd, "Tickets"
}
}
//Tony

It can be indeed be dynamic, but it is inneficient. personally I have neve done it and will try not to. Check out comp.databases.ms-sqlserver. Oh i realize you already did and are doubleposting xD

This is first google result for "dynamic order by tsql". Looks it could be what yo are lookig for http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

 

[Jeff Johnson]

It can be indeed be dynamic, but it is inneficient. personally I have neve
done it and will try not to.

Holy crap, it DOES work! For some reason I thought ORDER BY was like FROM in
that you have to build SQL statements manually to get dynamic behavior.