Using case Syria to do SQL's motion arrangement

SQL Server uses a random way to read data, so the user will use an order clause to arrange the data, and in this article, we're going to come up with a
How to use the case to do motion arrangement

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
Yes

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, such as the previous
For example, what do you want to do according to the company name?

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, suppose we want to put the staff (Employees) data table in English
Called the Taiwan style, you can do it

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!