Fundamentals of Transact-SQL

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:10

0
43
lượt xem
10

Fundamentals of Transact-SQL

Mô tả tài liệu

Fundamentals of Transact-SQL In this section, you'll learn some of the essential programming constructs available in TSQL. Specifically, you'll see how to use variables, comments

Chủ đề:

Bình luận(0)

Lưu

Nội dung Text: Fundamentals of Transact-SQL

1. Fundamentals of Transact-SQL In this section, you'll learn some of the essential programming constructs available in T- SQL. Specifically, you'll see how to use variables, comments, and conditional logic. You'll also see how to use a number of statements that allow you to perform jumps and loops. Finally, you'll examine cursors, which allow you to process rows returned from the database one at a time. Let's start by looking at variables. Using Variables A variable allows you to store a value in the memory of a computer. Each variable has a type that indicates the kind of value that will be stored in that variable. You can use any of the types shown earlier in Table 2.3 of Chapter 2, "Introduction to Databases." You declare a variable using the DECLARE statement, followed by the variable name and the type. You place an at character (@) before the start of the variable name. The following syntax illustrates the use of the DECLARE statement: DECLARE @name type Where name is the name of your variable, and type is the variable type. For example, the following statements declare two variables named MyProductName and MyProductID: DECLARE @MyProductName nvarchar(40) DECLARE @MyProductID int As you can see, MyProductName is of the nvarchar type, and MyProductID is of the int type. You can place more than one variable declaration on the same line. For example: DECLARE @MyProductName nvarchar(40), @MyProductID int Variables are initially set to null. You set a variable's value using the SET statement. For example, the following statements set MyProductName to Chai and MyProductID to 7: SET @MyProductName = 'Chai' SET @MyProductID = 7
4. Where statements1 and statements2 are multiple statements. You can also use an optional ELSE statement to execute a different branch of code if the condition is false. Note You can nest IF statements to any level. The following example displays the ProductID, ProductName, and UnitPrice columns for any rows from the Products table that have a UnitPrice of less than $5. You'll notice the use of the PRINT statement to output a line in this example. IF (SELECT COUNT(*) FROM Products WHERE UnitPrice < 5) > 0 BEGIN PRINT 'The following products have a UnitPrice of less than$5:' SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice < 5 END ELSE BEGIN PRINT 'There are no products that have a UnitPrice of less than $5' END Using CASE Statements You use the CASE statement to compare a value against a list of values and execute one or more statements when a match is found. For example, the following CASE statement returns Massachusetts: CASE 'MA' WHEN 'CA' THEN 'California' WHEN 'MA' THEN 'Massachusetts' WHEN 'NY' THEN 'New York' END The next example uses a SELECT statement to retrieve the value Massachusetts returned by the CASE statement: DECLARE @State nchar(2) SET @State = 'MA' DECLARE @StateName nvarchar(15) SELECT CASE @State WHEN 'CA' THEN 'California' WHEN 'MA' THEN 'Massachusetts' WHEN 'NY' THEN 'New York' 5. END You can store the value retrieved by the SELECT statement in a variable, as shown in the next example: DECLARE @State nchar(2) SET @State = 'MA' DECLARE @StateName nvarchar(15) SELECT @StateName = CASE @State WHEN 'CA' THEN 'California' WHEN 'MA' THEN 'Massachusetts' WHEN 'NY' THEN 'New York' END PRINT @StateName The output from this example is as follows: Massachusetts You can also compare a column value in a CASE statement. For example: SELECT Price = CASE WHEN UnitPrice IS NULL THEN 'Unknown' WHEN UnitPrice < 10 THEN 'Less than$10' WHEN UnitPrice = 10 THEN '$10' ELSE 'Greater than$10' END FROM Products You'll notice from this example that you can also supply a catchall ELSE condition in a CASE statement. Using WHILE Loops You use a WHILE loop to run one or more statements multiple times. A WHILE loop runs until a specified condition evaluates to false. The syntax for a WHILE loop is as follows: WHILE condition statement