Shulou(Shulou.com)05/31 Report--

This article shows you how to understand the role of CASE expressions and use SQLServerCASE expressions instead of dynamic SQL, the content is concise and easy to understand, can definitely make your eyes bright, through the detailed introduction of this article, I hope you can get something.

SQLServerCASE expressions can replace dynamic SQL statements, so that a large number of update query operations can improve the efficiency of the query. The following editor will explain the functions of CASE expressions. How do I use SQLServerCASE expressions instead of dynamic SQL?

What are the functions of CASE expressions

The CASE statement is a very powerful and useful tool that you can use to solve your SQLServer query problems. You may already be adept at using it to simulate IF/ELSE clause processing when executing SELECT commands. However, its function is far from limited to this type of processing.

CASE expressions can be used to:

Avoid using cursor loops when updating rows

Perform special processing when using aggregate functions

Create dynamic ORDERBY and WHERE clauses without using dynamic SQL

Now let's look at some application examples:

First, create a new table named Customer and insert some rows:

CREATETABLEdbo.Customer

(

CustomeridINTIDENTITYPRIMARYKEY

FirstnameVARCHAR (40) NOTNULL

LastnameVARCHAR (40) NOTNULL

StatecodeVARCHAR (2) NOTNULL

TotalsalesmoneyNOTNULLDEFAULT0.00

)

INSERTINTOdbo.Customer (firstname,lastname,statecode,totalsales)

SELECT'Thomas','Jefferson','VA',100.00

INSERTINTOdbo.Customer (firstname,lastname,statecode,totalsales)

SELECT'John','Adams','MA',200.00

INSERTINTOdbo.Customer (firstname,lastname,statecode,totalsales)

SELECT'Paul','Revere','MA',300.00

INSERTINTOdbo.Customer (firstname,lastname,statecode,totalsales)

SELECT'Ben','Franklin','PA',400.00

GO

How to use SQLServerCASE expressions instead of dynamic SQL

Example one

Now there is a requirement that you need to add a status description column to the table to generate the desired report. Of course, you can scan the entire table with a pointer to update each row, but this takes a lot of time and degrades system performance. You can also create multiple UPDATE statements, but this is no better. However, you can use a UPDATE statement with CASE so that you can update the entire table efficiently with only one SET operation.

ALTERTABLEdbo.CustomerADDstatedescriptionVARCHAR (50) NULL

GO

UPDATEdbo.Customer

SETstateDescription=CASEWHENstatecode='MA'THEN'Massachusetts'

WHENstatecode='VA'THEN'Virginia'

WHENstatecode='PA'THEN'Pennsylvania'

ELSENULL

END

Example two

Now we have received a second request to report the total number of all customers, the total number of Massachusetts customers, and the average sales of all Massachusetts customers. Of course, we can limit the scope of the query to Massachusetts customers, but it is troublesome to get the total number of customers. To solve this problem, you can write a query expressed in CASE in the aggregate function to get the information about the Massachusetts customer:

SELECTCOUNT (*) ASTotalCustomers

SUM (CASEWHENstatecode='MA'THEN1ELSENULLEND) ASTotalMassCustomers

AVG (CASEWHENstatecode='MA'THENtotalsalesELSENULLEND) ASTotalMassSales

FROMdbo.Customer

Because null values are ignored when executing aggregate functions, we can easily get the total number we need.

Now there's a new request. We need a stored procedure that can be called by the application, but the user wants to be able to sort it through firstname or lastname. You may want to use dynamic SQL to solve this problem, but we can also use CASE instead of dynamic SQL:

CREATEPROCEDUREdbo.getCustomerData@sortbyVARCHAR (9), @ sortdirectionCHAR (4)

AS

SETnocountON

SELECTcustomerid,firstname,lastname,statecode,statedescription,totalsales

FROMdbo.Customer

ORDERBY

CASE@sortdirection

WHEN'asc'THEN

CASE@sortby

WHEN'firstname'THENfirstname

WHEN'lastname'THENlastname

END

END

ASC

CASE@sortdirection

WHEN'desc'THEN

CASE@sortby

WHEN'firstname'THENfirstname

WHEN'lastname'THENlastname

END

END

DESC

GO

EXECdbo.getCustomerData'lastname','desc'

Example 4

One last request. We need to modify the above stored procedure to find the customer through a specific state. If the customer status is omitted, the customer in all states is returned.

ALTERPROCEDUREdbo.getCustomerData@sortbyVARCHAR (9), @ sortdirectionCHAR (4), @ statecodeVARCHAR (2) = NULL

AS

SETnocountON

SELECTcustomerid,firstname,lastname,statecode,statedescription,totalsales

FROMdbo.Customer

WHEREstatecode=CASEWHEN@statecodeISNOTNULLTHEN@statecode

ELSEstatecode

END

ORDERBY

CASE@sortdirection

WHEN'asc'THEN

CASE@sortby

WHEN'firstname'THENfirstname

WHEN'lastname'THENlastname

END

END

ASC

CASE@sortdirection

WHEN'desc'THEN

CASE@sortby

WHEN'firstname'THENfirstname

WHEN'lastname'THENlastname

END

END

DESC

GO

EXECdbo.getCustomerData'lastname','desc','MA'

The above is how to understand the role of CASE expressions and how to use SQLServerCASE expressions instead of dynamic SQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.