Using case narration to do the kinetic arrangement of SQL

Source: Internet
Author: User

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!

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.