Fundamentals of Transact-SQL

Fundamentals of Transact-SQL In this section, youll learn some of the essential programming constructs available in TSQL. Specifically, youll see how to use variables, comments
Nội dung trích xuất từ tài liệu:
Fundamentals of Transact-SQLFundamentals of Transact-SQLIn this section, youll learn some of the essential programming constructs available in T-SQL. Specifically, youll see how to use variables, comments, and conditional logic.Youll also see how to use a number of statements that allow you to perform jumps andloops. Finally, youll examine cursors, which allow you to process rows returned from thedatabase one at a time.Lets start by looking at variables.Using VariablesA variable allows you to store a value in the memory of a computer. Each variable has atype that indicates the kind of value that will be stored in that variable. You can use anyof 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 nameand the type. You place an at character (@) before the start of the variable name. Thefollowing syntax illustrates the use of the DECLARE statement:DECLARE @name typeWhere name is the name of your variable, and type is the variable type.For example, the following statements declare two variables named MyProductName andMyProductID:DECLARE @MyProductName nvarchar(40)DECLARE @MyProductID intAs you can see, MyProductName is of the nvarchar type, and MyProductID is of the inttype.You can place more than one variable declaration on the same line. For example:DECLARE @MyProductName nvarchar(40), @MyProductID intVariables are initially set to null. You set a variables value using the SET statement. Forexample, the following statements set MyProductName to Chai and MyProductID to 7:SET @MyProductName = ChaiSET @MyProductID = 7The following SELECT statement then uses these variables in the WHERE clause:SELECT ProductID, ProductName, UnitPriceFROM ProductsWHERE ProductID = @MyProductIDOR ProductName = @MyProductName;You can execute T-SQL using Query Analyzer, and Figure 4.1 shows the output from theexamples shown in this section.Figure 4.1: Executing T-SQL using Query AnalyzerUsing CommentsYou add comments to describe your code, making it more understandable for bothyourself and other programmers. You might think you understand your own code insideout, but when you return to it for maintenance six months later, you might have forgottenthe intricacies of your own creation! The point is that you should add comments to yourcode to aid understanding, but dont think you have to comment every line. Usecomments judiciously.You need to mark your comments with specific characters so SQL Server ignores themand doesnt try to process them as code. There are two types of comments: single-line andmulti-line. A single-line comment uses two negative signs (--) and may span only oneline, as shown here:-- A single-line comment may only span one line.The -- tells SQL Server to ignore everything up to the end of that line.A multi-line comment begins with an open comment mark (/*) and ends with a closecomment mark (*/):/* A multi-line comment may span more than one line. */The /* tells SQL Server to ignore everything up to the next */ mark, no matter how manylines forward it is. If you were to use single-line comments in this example, you wouldhave to add -- characters at the beginning of every line that made up the comment.Multi-line comments can of course also span only one line:/* Another comment */Using Conditional LogicConditional logic allows you to execute different branches of code based on the Booleantrue or false value of a given expression. For example, you might want to check if anerror condition is true and display a message. You use the IF and optional ELSEkeywords to perform conditional logic. The following syntax illustrates the use ofconditional logic:IF condition statement1[ELSE statement2]Where condition is a Boolean expression that evaluates to true or false. If condition istrue, then statement1 is executed, otherwise statement2 is executed.Note You can replace a single statement with multiple statements by placing those statements within BEGIN and END statements. This rule applies to all T-SQL programming constructs.The following syntax shows the replacement of single statements with a block ofstatements placed within BEGIN and END:IF conditionBEGIN statements1ENDELSEBEGIN statements2ENDWhere statements1 and statements2 are multiple statements. You can also use an optionalELSE 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 forany rows from the Products table that have a UnitPrice of less than $5. Youll notice theuse of the PRINT statement to output a line in this example.IF (SELECT COUNT(*) FROM Products WHERE UnitPrice < 5) > 0BEGIN PRINT The following products have a UnitPric ...