tailieunhanh - Fundamentals of Transact-SQL

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 | 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 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 The following SELECT statement then uses these variables in the WHERE clause SELECT ProductID ProductName UnitPrice FROM Products WHERE ProductID @MyProductID OR ProductName @MyProductName You can execute T-SQL using Query Analyzer and Figure shows the output from the examples shown in this section. Figure Executing T-SQL using Query Analyzer Using Comments You add .