20. Understand the advanced T-SQL
INSERT statement
Update statement
DELETE statement
Program keyword
Summary of this chapter
This chapter will discuss the T-SQL statement used by the previous section to modify the data, plus some T-SQL keywords that can be used to control the program. These statements and keywords can be used in any place where T-SQL can be used, such as in the instruction line, instruction code, pre-deployment, batch files, and applications. We will study the statement of information processing, such as INSERT, UPDATE, and DELETE (these statements have been mentioned in Chapter 13), and program structure, such as IF, while and case.
First we build a data sheet for the example, named Items (please build the data table in the MYDB database), the following is the T-SQL command for establishing the items data sheet:
Use mydb
Go
CREATE TABLE ITEMS
(
Item_category char (20) Not null,
Item_id Smallint Not Null,
Price Smallmoney Null,
Item_Desc varchar (30) Default 'NO DESC'
)
Go
Item_ID data line is suitable for the Identity property (see
INSERT statement
At 13 chapters have been introduced to INSERT statements, which can be used to add information columns to the information list or view table. The following is the basic syntax of INSERT:
INSERT [INTO] TABLE_NAME [(Column_List)] VALUES
Expression | Derived_Table
The INTO keyword and data line list can be set as needed. The data line list parameters specify the data line to insert, these values and the values listed in the arithmetic correspondence (or list only the list of values).
Insert information columns
The following example demonstrates how to insert information listed to the Items Data Sheet.
INSERT ITO ITEMS
(item_category, item_id, price, item_desc)
VALUES ('Health Food', 1, 4.00, 'TOFU 6 oz.')
Go
Since each of the data has been specified for each of the data sheets, the corresponding value is listed in the order defined in the data sheet, so we can also use the information line list. However, if the value is not in the order of the dependent material, the input information may be incorrect, or an error message is received. For example, try to perform the following statement, it will receive an error message:
INSERT ITO ITEMS
VALUES (1, 'Health Food', 4.00, 'TOFU 6 oz.')
Go
Server: message 245, level 16, state 1, line 1
Convert VARCHAR value 'health food' into data-based non-smallint
error.
Since the order placed in the value is incorrect, the information column is not inserted and the error message is returned. We tried to insert item_id into the item_category data line, Item_category inserted the item_id data line (the order of two data entered), two different data-type information is not compatible, of course, if the two data lines are Even if the value is different, the information will be inserted, but the information is inserted, but is incorrect. To see how the inserted column is displayed in the data sheet, use the following SELECT statement query information table to select all columns.
SELECT * from Items
Go
Will see the following result set:
Item_category item_id price item_desc
----------------------------------
Health Food 1 4.00 TOFU 6 oz.
When the Items data sheet is established, the Price Data is defined as allowing the NULL value, and the preset value of the Item_Desc (description) data is NO DESC. If the INSERT statement does not specify a value for the Price Data, null will be inserted in the new data column; if the item_desc data line does not specify a value, the preset value NO DESC will be inserted.
Omit money value
In the previous INSERT statement example, since the Price Data Row and the Item_Desc data are provided with a preset value, the value and name of the data line can be omitted. If you want to omit the value of the data line, you must specify parameters for other data lines in the data line. Otherwise, the listed values will be assigned to each data line according to the order defined in the data sheet.
For example, we entered this as soon as possible in the information line list, and there is no value for the data line list.
INSERT ITO ITEMS
VALUES ('Junk Food', 2, 'Fried Pork Skins')
Go
SQL Server will insert the value (Fried Pork Skins) provided by the item_desc (the third value in the value list) into the Price Data Line (the third field in the data sheet). Due to the PRICE Data Behavior SmallMoney Data, Fried Pork Skins is a CHAR data type, two incompatible data type, which will generate the following error message:
Server: message 213, level 16, status 4, line 1
Insert error: Data row name or the number of values provided is not consistent with the data sheet definition.
Imvolution If the data type of Fried Pork Skins is compatible, the information will be incorrectly inserted and also affects the consistency and correctness of the entire data sheet.
Remember that the data type of the inserted data sheet or the value checklist must be compatible with the data type defined by the field. Also, if the inserted data column violates rules or conditional constraints, an error message will generate an error message, and the data column will not be inserted.
In order to avoid incompatible data type, the specified data line list is specified to comply with the value that should be inserted, as shown below:
INSERT ITO ITEMS
(item_category, item_id, item_desc)
VALUES ('Junk Food', 2, 'Fried Pork Skins')
Go
Due to the specified price, the Price data line will insert a null value. Execute the following SELECT statement:
Select * from items should appear the result sets, the result set contains two data columns that have been inserted, pay attention to NULL in the Price data column.
Item_category item_id price item_desc
-----------------------------------------
Health Food 1 4.00 TOFU 6 oz.
Junk Food 2 Null Fried Pork Skins
Now add another information column, do not specify the value of the Price Data Line or the Item_Desc Data, as shown below:
INSERT ITO ITEMS
(Item_category, item_id)
VALUES ('Toy', 3)
Go
You can get the result set of this column using the following query:
SELECT * from items where item_id = 3
The result set as follows:
Item_category item_id price item_desc
-------------- ------------------------
TOYS 3 NULL NO DESC
Note NU DESC in the NULL and Item_Desc data columns in the Price Data Row. These values can be changed in this chapter will be described later.
There are four types of data, when there is no specified value, SQL Server automatically provides values to the data line: Allow NULL values, setting preset values, Identity Properties, and TimeStamp. Data lines that allow NULL values to be discussed prior to previously discussed the information line with preset values. If there is no specified value (discussed in Chapter 10). In most cases, it is not possible to manually insert the data value to these two types of data lines.
Description
Be cautious when performing Insert operation in the data sheet. Please make sure that the inserted data is properly placed in the data that should be inserted. After identifying all T-SQL program code, access or modify the information.
Add data columns from another information table
You can insert information from the data sheet to another. This can be done through the use of the derivative data sheet in the Insert statement, or using the Execute clause in the pre-depiction of the data column.
Description
Derived Table, is a Select statement result set from another T-SQL statement from the Sentence of the FROM clause, which will be discussed to the derivative data in Chapter 14.
Now demonstrate how to use the derivative data sheet into the information. First establish a data sheet, named two_newest_items, and insert the two data columns in the ITEM table into Two_Newest_Items, the following is the Create Table statement used by the new information table:
CREATE TABLE TWO_NEWEST_ITEMS
(
Item_id Smallint Not Null,
Item_Desc varchar (30) Default 'NO DESC'
)
Go
To insert the latest value in the Item_ID and Item_Desc Data from the Items Data Sheet, use the INSERT statement:
INSERT INTO TWO_NEWEST_ITEMS
(item_id, item_desc)
Select Top 2 item_id, item_desc from items
Order by item_id desc
Go
Note that the SELECT statement is used here, not the value lists listed in Insert. SELECT statement is submitted back from an existing information list, and the information transmitted is a list of values. In addition, it is noted that there is no parentheit in the SELECT statement, as parentheses will result in a transcript error. To check all the information columns in the new information list, you can use the following code:
SELECT * from TWO_NEWEST_ITEMS
The result set shown is as follows:
Item_id item_desc
-------------------------
3 NO DESC
2 Fried Pork Skins
Note that we use the ORDER BY clause in the Insert statement here to decrease the data column of item_id.
If we establish the previous SELECT statement as a pre-store, use the Execute statement to add the pre-stored program name, or you can get the same result (the pre-depression will be introduced in the 21st chapter). The method of operation is, first delete all the information columns in the Two_Newest_Items data sheet using the Delete statement. (For detailed details, please refer to this chapter later
Delete from Two_newest_Items
Go
Create Procedure Top_Two
AS
Select Top 2 item_id, item_desc from items
Order by item_id desc
Go
INSERT INTO TWO_NEWEST_ITEMS
(item_id, item_desc)
Execute top_two
Go
It can now be seen that we have successfully inserted two new information columns using top_two pre-depreciation.
relevant information
INSERT statements can be used to specify Table Hints. To learn more about the information sheet you can use with the Insert statement, use the "Online Book" to enter the lock prompt in the Synamic Label page, select the lock prompt theme.
Update statement UPDATE statement is used to modify or update the existence of information, the basic syntax is as follows:
UpdateTable_namesetColumn_Name = Expression
[FrOrbe_source] where search_condition
Update Information Columns This example will use the previous Items sample table, first update the Junk Food data column that is not entered before the price is updated. To identify the information column, specify the search criteria as Fried Pork Skins, update the price to $ 2 with the following statement:
Update items set price = 2.00
Where item_desc = 'fried Pork Skins'
Go
Then use the query to select the Junk Food Profile:
SELECT * from Items
Where item_desc = 'fried Pork Skins'
Go
The JUNK FOOD data column output is shown below, the original NULL value is replaced by 2.00:
Item_category item_id price item_desc
----------------------------------------------
JUNK FOOD 2 2.00 Fried Pork Skins You can also use the following statements to increase the price by 10%:
Update items set price = price * 1.10
Where item_desc = 'fried Pork Skins'
Go
Now if you choose a Junk Food data column, the price has changed to $ 2.20 ($ 2 multiplying 1.10 results); the price of other projects remains unchanged. Use Update statements to update more than one data column. For example, the price of all data columns can be increased by 10%, and the statement is as follows:
Update items set price = price * 1.10
Go
The resulting output is as follows:
Item_category item_id price item_desc
---------------------------------------------
Health Food 1 4.40 TOFU 6 oz.
Junk Food 2 2.42 Fried Pork Skins
TOYS 3 NULL NO DESC
The data set to NULL will not change (because NULL * 1.10 is still equal to NULL), such an execution method is acceptable and does not generate an error message. Using the from clause Update statement can be used to specify the source of information in the update operation, the data source list can include the data table name, checklist name, information column, and function, derivative data sheet and joint data sheet, even updated The data sheet can also be used as a source of information. The following is an example, first establish a new information table using the Create Table statement, name TAX, and insert a new information column with Insert statement, set the value to 5.25 in the TAX_PERCENT Data Line.
CREATE TABLE TAX
(
TAX_PERCENT REAL NOT NULL,
Change_date smalldatetime default getdate ()
)
Go
INSERT INTO TAX
(Tax_Percent) VALUES (5.25)
Go
Due to the date and time, the Change_Date information will use the preset value, and the getDate function gets the current date and time. Now add a new data line in the Item data table, named price_with_tax, and allow NULL values, the statement is as follows:
Alter Table Items
Add Price_with_Tax SmallMoney Null
Go
Next to update all Price_With_TAX data lines, the information on this data is calculated by Items.Price * Tax.Tax_PERCENT. Use the UPDATE statement and from clauses as follows:
Update items
Set price_with_tax = i.price
(i.price * t.tax_percent / 100)
From items i, TAX T
Go
There is a value of the price of the Price_With_TAX data, and the value of the calculation will occur; if the original Price Data line is NULL, the corresponding price_with_tax will also appear NULL (NULL Multiply by any value or null) The result set after the operation is as follows (the result also contains all previously modifications made by the data): item_category item_id price item_desc price_with_tax
-------------- -------------------------------- ------------
Health Food 1 4.40 TOFU 6 oz 4.63
Junk Food 2 2.42 Fried Pork Skin 2.55
TOYS 3 NULL NO DESC NULL
If you add a record in the data sheet, you can consider the previous Update statement, but this will update the Price_With_TAX data line again, and the process time is wasted. To avoid this, you can use the Update statement of the WHERE clause to let SQL Server update the provisions of the NULL value in Price_with_tax.
Update items
Set price_with_tax = i.price
(i.price * t.tax_percent / 100)
From items i, TAX T
Where i.price_with_tax is null
Go
Update statement is ideal for trigger programs, will execute when inserting a value to the Price Data Row. The trigger program is a special pre-deployment that is automatically executed under a particular condition. The trigger program will be discussed in the twenty-second chapter.
Derived table
Another way to use Update statement is to come with a derived data sheet or subquery in the FROM clause. The derivatic table can then be input as an external UPDATE. Using example, we will use the Two_newest_items data table in the subquery, using the items data sheet in the external UPDATE statement. We want to update two of the items in the items tab, so that the price_with_tax data line stores the null value, you can use the query two_newest_items data table to find the item_id value in the information column:
Update items
Set price_with_tax = null
From (SELECT ITEM_ID from Two_Newest_Items) AS T1
Where items.Item_id = t1.item_id
Go
SELECT statement is made as a subquery, and the result will be placed in a temporary survived data name named T1, which is treated by the WHERE clause as a search condition. The result of the subquery produces two values of Item_ID, as long as there are 2 or 3 in the Item_ID data line, it will be affected. In the data column of the item_id value of 3, since the corresponding Price_With_Tax data has a NULL value, it will not change again; and the data column at the ITEM_ID value is 2 is updated to NULL value. The result set is as follows:
Item_category item_id price item_desc price_with_tax
----------------------------------- -----
Health Food 1 4.40 TOFU 6 oz. 2.30junk Food 2 2.42 Fried Pork Skins Null
TOYS 3 NULL NO DESC NULL
DELETE statements DELETE statements are used to remove (delete) one or more data columns from a data sheet or view table. Delete does not affect the data sheet definition; just remove the information column from the data sheet. The basic syntax of Delete is as follows:
Delete [from] Table_Name | View_name
[FrOrble_sources] Wheresearch_condition
The first and second from keywords are visible. In the second FROM clause, the information column in the data sheet source will not be deleted, but it is deleted from the table_name or view_name specified after Delete. Deleting Individual Information Columns Using the WHERE clause in Delete, you can specify that you can delete a specific information column from the data sheet. For example, to remove this data column in item_category from the Items data table, perform the following description:
Delete from items
Where it item_category = 'Toy'
Go
This statement has removed a column from the Items data list. Use one or more of the second FROM clauses of the source to specify other information sheets or view tables for WHERE search criteria. For example, to remove a list of data in the Two_Newest_Items data sheet from the Items table, you can perform the following statements:
Delete Items
From two_newest_items
Where items.Item_id = two_newest_items.Item_ID
Go
Note that in this statement, the Item_ID data column in the Two_newest_items data sheet contains a value of 2 and 3, and the Item_ID data column in the Items data sheet contains 1 and 2 value, so 2 is deleted (because of the search condition). Two data sheets in the Two_newest_items data sheet (source) are not affected. Delete All information columns To delete all the information columns in the data sheet, you can use the Delete statement. The following DELETE statements will delete all the information columns in the Two_newest_Items Data Sheet:
Delete from Two_newest_Items
Go
Now the Two_newest_items data sheet is an empty data sheet, that is, the information sheet does not contain the information column. If you want to delete a data sheet definition, use the Drop Table instruction description in Chapter 15:
DROP TABLE TWO_NEWEST_ITEMS
Go
relevant information
More ways to use DELETE can be found in "Online Book", such as using joint data tables as a source of information, and use the information list and query prompts, please consult the Delete in the "Online Book" index and select the relevant topic.
Programming Keyword T-SQL statement has several useful programming structural keywords, which can be used to control program flows. These structures can be used for batch processing (a set of T-SQL statements at a time), pre-deposit programs, instruction codes, and special queries (this section will use the PUBS database). IF ... Else If ... ELSE Structure Utilization Conditions to decide to execute the T-SQL statement, the syntax is as follows:
Ifboolean_expression
T-SQL_STATEMENT | Block_Of_Statements
[Elset-SQL_Statement | Block_Of_Statements] Boolean Expression refers to the operation of the True or False. If the arithmetic in the IF clause passes back TRUE, the statement will be executed, the ELSE clause and its narrative are not executed; if the operation is returned to FALSE, only the description of the ELSE keyword will be executed. The statement block (block_of_statements) represents more than one T-SQL statement. When using the statement block, whether the statement block is in the IF clause, both the ELSE clause or both must be used to specify the beginning and end of the block with the keyword begin and end. You can also use the IF clause without an else clause. Now let's take a look at the example of using only the IF clause, the following program code will check the arithmetic, if the operation is returned to true, will execute the next Print statement:
IF (Select Ytd_sales from Titles
Where title_id = 'pc1035')> 5000
Print 'Year-to-Date Sales Are
Greater Than $ 5,000 for pc1035. '
Go
Because the value of Title_ID = 'PC1035' Number YTD_SAles is 8780 (greater than 5000), the IF operation will be calculated as True, and the Print statement is executed, "Year-to-Date Sales Area Greater Than $ 5,000 for PC1035" On the screen. Now add an ELSE clause to the above example and turn> 5000 to> 9000. If ... ELSE is shown below:
IF (Select Ytd_sales from Titles
Where title_id = 'pc1035')> 9000
Print 'Yeartodate Sales Are
Greater Than $ 9,000 for pc1035. '
Else
Print 'Yeartodate Sales Are
Less Than $ 9,000 for pc1035. '
Go
In this example, since the IF operator is returned to FALSE. The print statement after the ELSE clause will be executed. Let us further expand the above examples and add state blocks in the IF and Else clauses. According to the result of the IF condition, the query is executed after the print message, the following is the sample code code:
IF (select ytd_sales from titles where title_id = 'pc1035')> 9000
Begin
Print 'Year-to-Date Sales Are
Greater Than $ 9,000 for pc1035. '
SELECT YTD_SALES from Titles
Where title_id = 'pc1035'
End
Else - YTD_SALES must be small and or equal to 9000.
Begin
Print 'Year-to-Date Sales Are
Less tour or equal to $ 9,000 for pc1035. '
Select Price from Titles
Where title_id = 'pc1035'
End
Go
The IF calculating result is False, so the narrative between BeGin and End is performed in the ELSE clause: After performing Print, then execute the SELECT statement, the display price is $ 22.95. You can also use a nestix IF statement after the IF clause or the else clause. For example, the following program code can be performed, using a nestix IF ... Else statement to find the range of YTD_SAles average: IF (SELECT AVG (YTD_Sales) from titles <10000
IF (SELECT AVG (YTD_SALES) from Titles <5000
IF (SELECT AVG (YTD_SALES) from Titles <2000
Print 'Average Year-to-Date Sales Are
Less Than $ 2,000. '
Else
Print 'average years-to-date_ sales area
Between $ 2,000 and $ 4,999. '
Else
Print 'Average Year-to-Date Sales Are
Between $ 5,000 and $ 9,999. '
Else
Print 'average year-to-date sales area greater
Than $ 9,999. '
Go
When you perform the program code, you will see this message: "Warning: AGGREGATE) or other SET operation has eliminated the NULL value." The message indicates the average number of data sheets, in YTD_SAles The NULL value is not used, nor is it calculated as a value. Since the average is $ 6,090, the final result is "Average Year-To-Date Sales Are Between $ 5,000 and $ 9,999." Please note that when using the IF statement, it is easy to confuse the IF as a set of IF, or if there is IF but forgets the indication ELSE, so use the Tab key (as shown above) is a good method. While This condition is determined by setting the condition that the SQL statement or statement is repeatedly performed. As long as the specified condition is True, the statement will be repeated. This is the so-called WHILE loop (loop) because the program code in the While structure is repeated in the back ring. The syntax is shown below:
WhileBoolean_EXPIXPRESSION
SQL_STATEMENT | Block_Of_Statements
[BREAK] SQL_STATEMENT | Block_Of_Statements
[Continue]
With the above IF ... ELSE, use Begin and End to specify the start and end of the statement in the WHILE loop. Break Keywords will result in jump out of the While round ring, and then execute the statement after the end of the WHILE loop. If a While rebound ring is a nest structure, the Break keyword will only be jumped out of the WHILE loop, continue to perform all the statements and loopes outside the loop. Continue Keyword Specifies the While Reallo Re-executes the statement between Begin and End to ignore other statements after Continue. Now use a simple WHILE loop repeat the Update statement. The detection condition of the WHILE ring ring is: whether the average of the RoyalTY data line is less than 20. If the detection is returned to True, all the values of all RoyalTy data will increase by 5%. Then I will detect the While condition again, repeat the update until the average of the RoyalTy field is 20 or more. The program code is as follows:
While (SELECT AVG (Royalty) from roysched <20UPDATE ROYSCHET ROYALTY = Royalty * 1.05
Go
Since the RoyalTY data line has a large average of 15, the WHILE ring has been executed 21 times before the average value reaches 20, until the detection condition is returned to FALSE. Now look at an example of using Break, Continue, Begin, and End in the While Reynoe. We will perform a round of round until the average of the RoyalTy reaches 25. However, if in the loop, the maximum of Royal is more than 27, and the loop will be interrupted, and a SELECT statement is added after the WHILE ring. Here is the T-SQL program code:
While (SELECT AVG (Royalty) from ROYSCHED <25
Begin
Update roysched set royalty = royalty * 1.05
IF (SELECT MAX (Royalty) from ROYSCHED> 27
Break
Else
Continue
End
SELECT MAX (Royalty) AS "Max Royal" from ROYSCHED
Go
The RoyalTY value already existing in the data sheet is greater than 27, so only the loop is executed, and the average of RoyalTY is less than 25%, so Update is executed once; then the IF statement detects and passes back True, so executes Break, jump out of the WHILE ring ring Finally, the statement of the END key is executed, that is, the SELECT statement. You can also use a nest while ring, but remember that Break or Continue key is applied to call them back, not external While recoil. The CASE CASE keyword is used to evaluate a list of various conditions, and the multi-possible result arithmetic shape is transmitted. The most common usage is used to replace the program, or the value (abbreviated value), Or used to classify values, the example of this section will be described. CASE is divided into two formats: Simple format and search (Searched) format. Simple format uses CASE's arithmetic, compared to a set of arithmeters after the WHEN to determine its results; search formats evaluate a set of cloth operations for TRUE or FALSE to determine its results. The following is the syntax of simple format:
Case Input_Expression
Whenime_Expression Then Result_Expression
[When when _expression the result_expression ... n]
[ELSE ELSE_RESULT_EXPRESSION]
End
WHEN_EXPRESSION and INPUT_EXPRESSION are compared in the arrangement order in the CASE clause, if the corresponding WHEN_EXPRESSION is equal to INPUT_EXPRESSION, will return the Result_Expression value. If there is no corresponding value, the value of the specified ELSE_RESULT_EXPRESSION will be returned. If you do not specify ELSE_RESULT_EXPRESSION, it is returned to NULL. Note that in the simple format case clause, the data type of the input_expression value and the WHEN_EXPRESSION value must be the same, or the data type is allowed to implicit conversion. The following example uses a Case clause in the simple format in the SELECT statement. The PayTERMS data for the Sales table contains Net 30, Net 60, ON Invoice or NONE. This T-SQL statement allows the use of alternative (easy to understand) to display the Payterms data line: SELECT 'payment terms' =
Case payterms
When 'Net 30' Ten 'Payable 30 Days
After invoice '
When 'Net 60' Ten 'Payable 60 Days
After invoice '
When 'on invoice' TEN 'PAYABLE UPON
Receipt of infoice
Else 'None'
End,
Title_id
From sales
Order by payterms
Go
The CASE clause detects the value of each of the data columns in the Select statement in the Payterms Data Line. When the when_expression value is equal to the Payterms value, the value of Result_expression is returned. The results from the CASE clause showed the Payment Terms Data Line in the result set, as shown below:
Payment Terms Title_ID
-----------------------------------------
Payable 30 Days After Invoice PC8888
Payable 30 Days After Invoice TC3218
Payable 30 Days After Invoice TC4203
Payable 30 Days After Invoice TC7777
Payable 30 Days After Invoice PS2091
Payable 30 Days After Invoice MC3021
Payable 30 Days After Invoice Bu1111
Payable 30 Days After Invoice PC1035
Payable 60 Days After Invoice Bu1032
Payable 60 Days After Invoice PS2091
Payable 60 Days After Invoice PS2091
Payable 60 Days After Invoice PS1372
Payable 60 Days After Invoice PS2106
Payable 60 Days After Invoice PS3333
Payable 60 Days After Invoice PS7777
Payable 60 Days After Invoice Bu7832
Payable 60 Days After Invoice MC2222
Payable Upon Receipt of Invoice PS2091Payable Upon Receipt of Invoice Bu2075
Payable Upon Receipt of Invoice MC3021
Payable Upon Receipt of Invoice Bu1032
(Impact 21 data columns)
Now let's take a look at the CASE clause of the search format, the syntax is as follows:
Case
WhenBoolean_ExpressionThenResult_Expression
[Henboolean_expressionThenResult_Expression ... n]
[ELSEELSE_RESULT_EXPRESSION]
End
Simple format and search format CASE clauses differ in: Search format Case clause does not have input_expression values after the Case keyword, and after the WHEN keyword detection True or False, not like a simple format comparative operation Whether it is equal. The CASE clause in the search format detects the boolean_expression value in the order in which each arithmetic arrangement is arranged, and the first Result_Expression value evaluated as True. For example, the CASE clause in the following SELECT statement will detect the Price value of each data column, and the price range in the book is used as the standard, and the price value in the data column is transmitted back:
SELECT 'Price Range' =
Case
When Price Between .01 and 10.00
Then 'INEXPENSIVE: $ 10.00 or Less'
When Price Between 10.01 and 20.00
THEN 'MODERATE: $ 10.01 to $ 20.00'
When Price Between 20.01 and 30.00
THEN 'SEMI-EXPENSIVE: $ 20.01 to $ 30.00'
When Price Between 30.01 and 50.00
Then 'Expensive: $ 30.01 to $ 50.00'
When Price Is Null
Then 'No Price Listed'
Else 'Very Expensive!'
End,
Title_id
From titles
Order by price
Go
The result set is shown below:
Price Range Title_ID
----------------------------------------
NO price listed mc3026
No Price Listed PC9999
INEXPENSIVE: $ 10 or Less MC3021
INEXPENSIVE: $ 10 or Less Bu2075
INEXPENSIVE: $ 10 or Less PS2106
INEXPENSIVE: $ 10 or Less PS7777
Model: $ 10.01 to $ 20 ps2091
Moderate: $ 10.01 to $ 20 bu1111
Model: $ 10.01 to $ 20 TC4203
Model: $ 10.01 to $ 20 tc7777
Moderate: $ 10.01 to $ 20 bu1032
Moderate: $ 10.01 to $ 20 bu7832modederate: $ 10.01 to $ 20 mc2222
Model: $ 10.01 to $ 20 ps3333
Moderate: $ 10.01 to $ 20 pc8888
SEMIEXPENSIVE: $ 20.01 to $ 30 TC3218
SEMIEXPENSIVE: $ 20.01 to $ 30 ps1372
SEMIEXPENSIVE: $ 20.01 to $ 30 pc1035
(Impact 18 data columns)
Description In the above two case clauses, since the entire CASE clause is part of the data line list in the SELECT clause, we will join the title_id data line after the END key is inserted. In other words, the entire CASE clause is just a project in the information line list. This is the most often used method for CASE keywords.
Other Keywords The following T-SQL keywords that can be used to control program processes: Goto Label: When a Label is defined in Goto, the oriented handler is directly processed by the label. Return: Unconditioned exits from queries or programs.
Waitfor: Sets the delay or specified time for the statement of execution.
Related Information To learn more about how to use these keywords, check the topics listed in the dialogue in the "Online Book" index.
This chapter summarizes this chapter that you have learned T-SQL statements using INSERT, UPDATE and DELETE, and keywords IF, Else, While, Begin, End, and Case, which are used to control program processes. Chapter 21 will learn how to establish a pre-deployment process, use these statements and structures.
