Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1165

how to use case in sql query

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

how to use case in sql query "Tony Johansson" <johansson.andersson@telia.com> - 2012-07-09 18:48 +0200
  Re: how to use case in sql query "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-07-09 15:13 -0400
  Re: how to use case in sql query Erland Sommarskog <esquel@sommarskog.se> - 2012-07-09 23:47 +0200

csiph-web