In public newsgroups, a recurring question is "How can I 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, with the If ... Else execute a pre-written query
For most people, the first thing you might think of is: through If ... Else statement, execute one of several pre-written queries. For example, suppose you want to query from the Northwind database for a sorted list of shippers (Shipper), the calling code specifies a column in the form of stored procedure parameters, and the stored procedure sorts the output based on that column. Listing 1 shows a possible implementation of this stored procedure (Getsortedshippers stored procedure).
"Listing 1: With If ... Else executes one of several pre-written queries in the "
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, ensuring that the code has optimal performance. The main disadvantage of this approach is that if the requirements of the query change, you have to modify multiple independent select queries--here are three.
http://www.bkjia.com/PHPjc/631158.html www.bkjia.com true http://www.bkjia.com/PHPjc/631158.html techarticle in public newsgroups, a recurring question is "How can I return a sorted output based on the parameters passed to the stored procedure?" ”。 With the help of some high-level experts, ...
