Dynamic ordering of database query results (1)

Dynamic | data | Database in a public newsgroup, a recurring problem is "How can you return a sorted output based on the parameters passed to the stored procedure?" ”。 With the help of some high-level experts, I have sorted out several solutions to this problem.


First, use if ... Else execute a prepared query


For most people, the first thing to think of might be: through if ... Else statement, executing one of several prepared queries. For example, suppose you want to get a sorted list of shippers (shipper) from an Northwind database query, and the calling code specifies a column in the form of a stored procedure parameter, and the stored procedure sorts the output according to that column. Listing 1 shows a possible implementation of this stored procedure (getsortedshippers stored procedures).



"Listing 1: Use If ... Else executes one of several prepared queries.


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 approach is that the code is simple and easy to understand, and the SQL Server query optimizer 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 request for a query changes, you must modify multiple separate select queries--Three here.