Using case narration to do the kinetic arrangement of SQL

SQL Server uses the random way to read data, so the user uses an order clause to arrange the data, and in this article, we're going to step through how to use the case to do an arrangement of motion.

ORDER BY clause

Let's take a look at the general use of the results of the Select, in this article for example I will use SQL Server's North Wind Library, please open Query Analyzer, execute

Use Northwind

Go

Select CompanyName, ContactName, ContactTitle

From Customers

The results may be ranked according to the data input, which is that you want to follow your own rules, you need to use the ORDER BY clause, for example, in the previous example, you have to follow the company name to do it

Select CompanyName, ContactName, ContactTitle

From Customers

ORDER BY CompanyName

Case Syria statement

Case Syria is appropriate to control the flow of the program, so we have to use the case Syria to do t-SQL logic control, to see the language of the Danish case

　　Case <input expression>
When <when expression> Then <result expression>
[... n]
[Else <result expression>]

It's not enough to look at the language, now with an example to practice, we still use the North Wind Data library, the assumption that we will be in the staff (Employees) data table in the English call to Taiwan-style, you can do

Select FirstName, titleofcourtesy, COURTESYINTC =

Case TitleOfCourtesy

When ' Ms. ' Then ' water Girl '

When ' Dr ' Then ' Dr. Bo '

When ' Mr. ' Then '

When ' Mrs. ' Then ' lady '

Else ' sir/Madam '

End

From Employees

Motion arrangement

Once we understand the basic language, we can start learning how to use ORDER by plus case Syria to do the dynamic Data arrangement.

Declare @OrderKey TinyInt

Set @OrderKey = 2

Select CompanyName, ContactName, ContactTitle

From Customers

ORDER BY case

When @OrderKey = 1 Then CompanyName

When @OrderKey = 2 Then ContactName

Else ContactTitle

End

But it doesn't really work, at least in the ASP, so let's do a program

Create Procedure Sp_selectcustomersdynamicorder

@OrderKey = Null

As

Select CompanyName, ContactName, ContactTitle

From Customers

ORDER BY case

When @OrderKey = 1 Then CompanyName

When @OrderKey = 2 Then ContactName

Else ContactTitle

End

Now you can directly call this Stored Procedure

EXEC Sp_selectcustomersdynamicorder

EXEC Sp_selectcustomersdynamicorder 1

EXEC Sp_selectcustomersdynamicorder 2

Is it a lot nicer to press?

Hopefully this article will help you!