Home >Backend Development >PHP Tutorial >Dynamic sorting of database query results (1)_PHP tutorial
Dynamic sorting of database query results (1)_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:01:341302browse
A frequently asked question in public newsgroups is "How can I return a sorted output based on the parameters passed to a stored procedure?". With the help of some high-level experts, I've put together several solutions to this problem.
1. Use IF...ELSE to execute pre-written queries
For most people, the first thing that comes to mind may be: execute several pre-written queries through IF...ELSE statements one of the. For example, suppose you want to query the Northwind database to get a sorted list of shippers. The calling code specifies a column in the form of a stored procedure parameter, and the stored procedure sorts the output results based on this column. Listing 1 shows one possible implementation of such a stored procedure (GetSortedShippers stored procedure).
[Listing 1: Execute one of multiple pre-written queries with IF...ELSE]
CREATE PROC GetSortedShippers
@OrdSeq AS int
AS
IF @OrdSeq = 1
SELECT * FROM Shippers ORDER BY ShipperID
ELSE IF @OrdSeq = 2
SELECT * FROM Shippers ORDER BY CompanyName
ELSE IF @OrdSeq = 3
SELECT * FROM Shippers ORDER BY Phone
The advantage of this method is that the code is very simple and easy to understand. The query optimizer of SQL Server can create a query optimization plan for each SELECT query to ensure that the code has optimal performance. The main disadvantage of this approach is that if the query requirements change, you must modify multiple independent SELECT queries - in this case, three.