On Day 16 you looked at batches and scripts. You then examined the different types of locking used in SQL Server for concurrency control and used your understanding of locking to implement transaction control.
Today's lesson will focus on the programming features of the Transact-SQL language. You will look at the programming constructs that allow you to make your SQL code much more like a true program, by using variables and Control Of Flow statements. You will finish this day by looking at cursors, which allow row-at-a-time processing of data, and examine the pros and cons of using them.
Within a SQL Server batch or stored procedure, variables can be used to hold values for later action. Two kinds of variables are supported: local and global. The distinction between the two is not the same as in some other programming languages. Local variables can be assigned values and used within batch statements, while global variables should really be called system variables because they are declared and assigned a value for you by SQL Server.
A local variable is just that, local to a batch of Transact-SQL statements or a stored procedure. It must be declared, assigned a value, and used all within the same batch or stored procedure. When the batch or stored procedure is finished, the variable disappears. A local variable must be declared with the DECLARE statement:
DECLARE @variable_name datatype [, @variable_name datatype...]
A variable can be declared to be any datatype except text or image, including user-defined datatypes. A variable always has only a single value; there are no array or structure variables in SQL Server.
To assign a value to a variable, you use a SELECT statement with the variable on the left-side of an equal sign.
DECLARE @value INT SELECT @value = 10
A variable can get its value from a database table. The following batch declares a variable, assigns to it the average book price in the titles table, and uses that variable in a where clause to return all the books that have a price greater than that average.
DECLARE @avgprice MONEY SELECT @avgprice = avg(price) FROM titles SELECT * FROM titles WHERE price > @avgprice
It is very important to remember that local variables have a scope of only a single batch (or stored procedure). If you broke the above batch into two, the second SELECT statement would return an error because the variable would no longer be recognized.
DECLARE @avgprice MONEY SELECT @avgprice = avg(price) FROM titles go SELECT * FROM titles WHERE price > @avgprice
More than one variable can be assigned in a single SELECT statement.
DECLARE @avgprice MONEY, @maxprice MONEY SELECT @avgprice = avg(price), @maxprice = max(price) FROM titles
However, you cannot combine assignment to variables and returning values to the client in the same SELECT statement. The following would generate an error.
DECLARE @avgprice MONEY SELECT @avgprice = avg(price), title FROM titles
If more than one value is returned by SELECT, the variable takes on the last valid value from the select list results.
In the following example, @price will end up with the last price in the table.
DECLARE @price MONEY SELECT @price = price FROM titles
If no values are returned by SELECT, the variable remains unchanged. So, if you executed the following statement immediately after the preceding one, the value of @price would not change, because there are no books of type "science" in the titles table.
SELECT @price = price FROM titles WHERE type = "science"
Global variables in SQL Server are not really comparable to global variables in other programming languages. They are really system variables that are declared and assigned a value for you. A global variable can be recognized because its name always starts with a double @ sign, as in @@version. All you can do with a global variable is examine its value. The list of global variables is predefined and available in the documentation.
Some global variables are very volatile and their value changes after almost every command. For example, @@rowcount always reflects the number of rows affected by the last command. You can see the value in a variable (either local or global) by simply selecting it.
SELECT pub_name FROM publishers WHERE state != `CA' SELECT @@rowcount pub_name ---------------------------------------- New Moon Books Binnet & Hardley Five Lakes Publishing Ramona Publishers Scootney Books (5 row(s) affected) ---------- 5 (1 row(s) affected)
ANALYSIS: The SELECT statement returned 5 rows, and reported that fact. The value of the variable @@rowcount then contained the value 5, which was displayed when you selected the variable to be displayed.
The value of @@rowcount changes with every SELECT statement. So, if you selected the value of @@rowcount twice, the value would change.
SELECT pub_name FROM publishers WHERE state != `CA' SELECT @@rowcount SELECT @@rowcount pub_name ---------------------------------------- New Moon Books Binnet & Hardley Five Lakes Publishing Ramona Publishers Scootney Books (5 row(s) affected) ---------- 5 (1 row(s) affected) ---------- 1 (1 row(s) affected)
ANALYSIS: The second time you selected @@rowcount, the value returned is 1, which is the number of rows affected by the previous statement, which is selecting @@rowcount and returning one row.
The global variable @@error is also very volatile and returns the error number generated by the previous statement. If no error occurred, @@error returns 0.
Some global variables are very static, and will never change during a session. For example, @@version contains a string representing the exact version of SQL Server being run, along with the operating system version and hardware platform. The value of @@version only changes when SQL Server is upgraded.
Some global variables can be changed by other statements that you execute. For example, @@servername can be affected by running the system procedure sp_addserver. The variable @@error can be affected by executing the RAISERROR statement (to be discussed later in today's lesson.) The variable @@fetch_status can be affected by fetching rows from an open cursor and will also be discussed later in today's lesson.
When a batch of statements are submitted to SQL Server for execution, the normal flow is for the command to be executed in the order they are given. The Transact-SQL language provides several commands that allow you to change the sequence of command execution.
Several of the control of flow commands that will be presented require a single statement as part of their syntax. Whenever a single statement is expected, you can actually use multiple statements if you enclose them between the keywords BEGIN and END. You will see this construct used in some of the examples below.
Up to this point, the only way you have been able to return any information from SQL Server to your client program is to use a SELECT statement. SQL Server also provides a PRINT statement, but its use is limited.
PRINT {`any ASCII text' | @local_variable | @@global_variable}
All that you can print is an ASCII string (string constant) or a variable of type character (either fixed or variable length). For example
PRINT "hello" PRINT @@version
If you want to print something more complex, you must build the string in a character variable, and then print that variable.
In the following example you will use @msg to build one big string to print. Concatenation operations and numeric variables cannot be used in a print statement, but you can use them in an assignment SELECT and then PRINT the result.
use pubs declare @msg varchar(50), @numWA tinyint SELECT @numWA = count(*) FROM stores WHERE state = "WA" SELECT @msg = `There are ` + convert(varchar(3),@numWA) + ` stores in Washington.' PRINT @msg (1 row(s) affected) (1 row(s) affected) There are 2 stores in Washington.
An IF...ELSE block allows a statement to be executed conditionally. The word IF is followed by an expression that must be either true or false. If the expression is true, the next statement is executed. The optional ELSE keyword introduces an alternate statement that is executed when the expression following the IF is false.
IF Boolean_expression {sql_statement | statement_block} ELSE [Boolean_expression] {sql_statement | statement_block}]
Following are some examples.
This statement will call the procedure do_weekly_report if today is Friday, otherwise no action will be taken.
IF (datename(dw, getdate()) = `Friday') exec do_weekly_report
This statement will call the procedure do_weekly_report if today is Friday and will also print a message.
IF (datename(dw, getdate()) = `Friday') BEGIN PRINT `Weekly report' exec do_weekly_report END
This statement will call the procedure do_weekly_report if today is Friday; otherwise it will call the procedure do_daily_report.
IF (datename(dw, getdate()) = `Friday') BEGIN PRINT `Weekly report' exec do_weekly_report END ELSE BEGIN PRINT `Daily report' exec do_daily_report END
Note that there is no specific keyword to mark the end of the IF...THEN block.
The Boolean expression that follows the IF can include a SELECT statement. If that SELECT statement returns a single value, it can be then compared with another value to produce a Boolean expression.
In the following example, if the average book price is greater than $15, you want to print one message, and, if the average book price is less than or equal to $15, you want to print a different message.
IF (SELECT avg(price) FROM titles) > $15 PRINT `Hold a big sale' ELSE PRINT `Time to raise prices'
If the SELECT statement that follows the IF returns more than one value, you can use a special form of IF, which is IF EXISTS.
IF EXISTS (SELECT statement) {sql_statement | statement_block} [ELSE {sql_statement | statement_block}]
IF EXISTS returns true if the SELECT statement which follows returns any rows at all, and returns false if the SELECT statement returns no rows.
The following example will return all the information about any book published by publisher with ID 9933.
IF exists (SELECT * FROM titles WHERE pub_id = `9933') BEGIN PRINT "Here are the books:" SELECT * FROM titles WHERE pub_id = `9933' END ELSE PRINT "No books from that publisher."
This query has the same results, but asks the opposite question:
IF not exists (SELECT * FROM titles WHERE pub_id = `9933') BEGIN PRINT "No books from that publisher" RETURN END PRINT "Here are the books:" SELECT * FROM titles WHERE pub_id = `9933'
In some cases IF EXISTS will have better performance than alternative methods, because SQL Server can stop processing as soon as the first row is found.
WARNING: Do not use IF EXISTS with aggregates because aggregates always return data, even if the value of that data is 0. To see if publisher 9933 has published any books, the following will NOT work.IF exists (SELECT count(*) FROM titles WHERE pub_id = `9933')This SELECT statement will always return one row. If there are no books by this publisher, that one row will have the value 0, but IF EXISTS will be true.
Programmers often want the ability to apply a conditional within another statement. The CASE expression allows SQL expressions to be simplified for conditional values. It allows the statement to return different values depending on the value of a controlling value or condition.
Simple CASE expression:
CASE expression WHEN expression1 THEN expression1 [[WHEN expression2 THEN expression2] [...]] [ELSE expressionN] END
Searched CASE expression:
CASE WHEN Boolean_expression1 THEN expression1 [[WHEN Boolean_expression2 THEN expression2] [...]] [ELSE expressionN] END
A simple CASE expression compares an initial expression with each expression in the list and returns the associated result expression. If none of the expressions match, the result expression after the word ELSE will be returned.
Here's an example:
SELECT title_id, type = case type WHEN `business' then `Business Book' WHEN `psychology' then `Psychology Book' WHEN `mod_cook' then `Modern Cooking Book' WHEN `trad_cook' then `Traditional Cooking Book' WHEN `popular_comp' then `Popular Computing Book' WHEN `undecided' then `No type determined yet' END FROM titles title_id type -------- ------------------------ BU1032 Business Book BU1111 Business Book BU2075 Business Book BU7832 Business Book MC2222 Modern Cooking Book MC3021 Modern Cooking Book MC3026 No type determined yet PC1035 Popular Computing Book PC8888 Popular Computing Book PC9999 Popular Computing Book PS1372 Psychology Book PS2091 Psychology Book PS2106 Psychology Book PS3333 Psychology Book PS7777 Psychology Book TC3218 Traditional Cooking Book TC4203 Traditional Cooking Book TC7777 Traditional Cooking Book (18 row(s) affected)
ANALYSIS: For each row in the titles table, the value in the type column is compared to each value in the CASE list. The first matching value determines what expression will be returned.
A searched case expression returns the expression associated with the first Boolean expression in the list that evaluates to true. If none of the expressions evaluates to true, the result expression after the word ELSE will be returned.
SELECT title_id, cost = case WHEN price <10 then `Cheap' WHEN price between 10 and 20 then `Midrange' ELSE `Expensive' END FROM titles title_id cost -------- -------- BU1032 Midrange BU1111 Midrange BU2075 Cheap BU7832 Midrange MC2222 Midrange MC3021 Cheap MC3026 Expensive PC1035 Expensive PC8888 Midrange PC9999 Expensive PS1372 Expensive PS2091 Midrange PS2106 Cheap PS3333 Midrange PS7777 Cheap TC3218 Expensive TC4203 Midrange TC7777 Midrange (18 row(s) affected)
ANALYSIS: For each row in the titles table, multiple expressions are evaluated. In this case, you are comparing the value of price against different values. The first expression that is true determines what expression will be returned.
Note that the simple CASE shown first is really just a special case of the searched CASE expression. You could have rewritten the first CASE as follows and gotten the same result.
SELECT title_id, type = case WHEN type = `business' then `Business Book' WHEN type = `psychology' then `Psychology Book' WHEN type = `mod_cook' then `Modern Cooking Book' WHEN type = `trad_cook' then `Traditional Cooking Book' WHEN type = `popular_comp' then `Popular Computing Book' ELSE `No type determined yet' END FROM titles
The simple form of the CASE expression can be used whenever all of the conditions are testing for equality.
There are two other simplifications that can be used instead of the basic CASE expression.
Sometimes a CASE expression will have the following form.
CASE WHEN expr1 is not null THEN expr1 [[WHEN expr2 is not null THEN expr2] [...]] [ELSE exprN] END
Here the value returned by the expression is the first non-null value in a list of values. An equivalent way of writing this would be to use the COALESCE function.
In this example, a wages table includes three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay and only one of the three columns will have a value in it. The other two columns will be NULL. The data will then look something like Table 17.1.
employee | hourly_wage | salary | commission | num_sales |
111 | null | 52000 | null | null |
112 | 14 | null | null | null |
113 | null | null | 0.15 | 500000 |
114 | null | 73000 | null | null |
115 | 4.90 | null | null | null |
116 | null | 28500 | null | null |
To determine the total amount paid to all employees, use the COALESCE function to receive only the non-null value found in hourly_wage, salary, and commission.
SELECT "Total Salary" = CONVERT(money, Â (COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales))) FROM wages
Sometimes a CASE expression will have the following form.
CASE WHEN expr1 = expr2 THEN null [ELSE expr1] END
Here the value returned by the expression is null if the two expressions are equivalent. Otherwise, the value of the first expression will be returned.
Suppose you have a table similar to the one in the example above; a wages table that includes three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay and only one of the three columns will have a value in it. In this table (shown in Table 17.2), the other two columns will be 0 (instead of NULL).
employee | hourly_wage | salary | commission | num_sales |
111 | 0 | 52000 | 0 | 0 |
112 | 14 | 0 | 0 | 0 |
113 | 0 | 0 | 0.15 | 500000 |
114 | 0 | 73000 | 0 | 0 |
115 | 4.90 | 0 | 0 | 0 |
116 | 0 | 28500 | 0 | 0 |
SELECT hourly_num = count(hourly_wage),salary_num = count(salary), commission_num = count(commission) FROM wage
The count( ) function counts how many values occur in a column and 0 counts as a value. The only thing that isn't included in the count( ) function is NULL values. So you can use the NULLIF function to turn the 0s into NULLs, and, for those values that aren't NULL, leave them alone.
SELECT hourly_num = count(nullif(hourly_wage,0)), Â salary_num = count(nullif(salary,0)), commission_num = count(nullif(commission,0)) FROM wage
The Transact-SQL language has a WHILE construct that allows repetitive execution until some condition is met. The use for this WHILE construct is limited in many cases, because, by its nature, SQL works with sets of rows. For example, you do not need a WHILE statement to loop through every row in a table; the SELECT statement itself steps through all the rows, testing your WHERE criteria for each row.
There are certain situations where you do need to repeat an action. The most common case is with cursors, which you should examine one row at a time. Cursors will be discussed a bit later in today's lesson.
A WHILE construct will repeat as long as a specified condition remains true. Just like with IF...THEN, if the condition includes a SELECT statement, the entire SELECT must be in parentheses.
In this example, you are repeatedly checking the average price of all books. As long as that average is less than $20, you will update the price of every book. The repetition will stop when the average price is greater than or equal to $20.
WHILE (SELECT avg(price) FROM titles ) < $20 BEGIN /* while */ UPDATE titles SET price = price * 1.1 PRINT "all prices have been increased by 10%" END /* while */
WARNING: As in any programming language, there is always the chance that the repetition could continue indefinitely. It is up to the programmer to make sure this doesn't happen.In the example, the repetition continues as long as the price is less than a specified amount, so there needs to be some action inside the loop that will raise the price; eventually the price will no longer be less than that specified amount. If the UPDATE in this example was decreasing the prices of the books, you would have an infinite loop.
You have already seen one use of the EXECUTE statement. It is used to invoke a stored procedure. SQL Server has another use for the keyword EXECUTE, and that is to create a dynamically executed command, one that is built immediately before it is run.
EXEC[ute] ({@str_var | `tsql_string'} [{@str_var | `tsql_string'}...)}
The string inside the parentheses must resolve to be a legal SQL statement, and a new batch is created in which to execute it. Any variables created inside the EXECUTE string are not available outside the EXECUTE.
The most common usage of this syntax is to allow you to parameterize object names. Normally, the names of tables and columns must be hard-coded into your queries, so that a stored procedure cannot accept a table name as a parameter. If you tried to do it, you would get an error.
create proc DisplayData (@TableName varchar(30) ) as SELECT * FROM @tablename Msg 170, Level 15, State 1 Line 3: Incorrect syntax near `@tablename'.
Using the dynamic EXECUTE you can construct the SQL statement as a string, and then pass that string as an argument to the EXECUTE.
create proc DisplayData (@TableName varchar(30) ) as EXECUTE (`SELECT * FROM ` + @tablename )
There are many more interesting things you can do with this capability to build strings dynamically. You will see an additional example after you take a look at cursors.
SQL Server provides the ability to generate and return your own error messages. You can generate an ad hoc message that will always be associated with error number 50000, or you can define your own error messages and refer to them by number. Your messages must always use error numbers higher than 50000.
RAISERROR ({msg_id | msg_str}, severity, state[, argument1 Â [, argument2]])[WITH options]
Note than the error messages can also take arguments. The message string should contain placeholders for these arguments as shown in the following examples.
This example uses a character argument (placeholder is %s) to return an ad hoc error message:
DECLARE @title_id char(6) SELECT @title_id = `BU0000' RAISERROR (`The title_id %s does not exist', 16, 1, @title_id) Msg 50000, Level 16, State 1 The title_id BU0000 does not exist
The next example first creates a new message with message number 55555, and then raises that error. The error takes two numeric arguments (placeholder is %d).
sp_addmessage 55555, 15, `The level for job_id %d should be between %d and %d RAISERROR (55555, 15, 1, @job_id, @min_lvl, @max_lvl)
Note the following points about raising errors:
The SQL statement you have been working with so far allows you to manipulate the data in a set-oriented manner. The SELECT, INSERT, UPDATE, and DELETE statements are designed to operate on sets of rows, applying conditions in the WHERE clause to determine which rows are included in the set. Once the WHERE clause determines the rows to include, you must then do the same thing to each row; for example, return each row in the set (SELECT), change the same column(s) in each row in the set (UPDATE), or remove each row in the set (DELETE).
The alternative to the set-oriented approach is to define a cursor, which allows us to step through rows one-at-atime. Based on the contents of the row, you can decide what further action you want to take.
WARNING: The performance overhead of having to process rows one-at-a-time (cursors) is enormous compared to overhead of dealing with rows with the set-oriented operations. Make sure you have carefully analyzed your needs, and cannot come up with any other programming technique to solve your problem, before you decide to use cursors. They can be useful in some situations, but they should be a last resort, not the first choice.
Five new commands are used to manipulate data through cursors: DECLARE, OPEN, FETCH, CLOSE, and DEALLOCATE. The flowchart in Figure 17.1 gives an overview of the way these commands work together.
Figure 17.1. The relationship between the cursor manipulation commands.
Before using a cursor, you must DECLARE it. The DECLARE statement associates the name of a cursor with a single SELECT statement. In addition, you can specify how you will be using the cursor.
Cursor options include:
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR SELECT_statement [FOR {READ ONLY | UPDATE [OF column_list]}]
The DECLARE statement will parse the cursor's SELECT statement, but SQL Server will not execute the statement or check permissions on the objects being accessed.
The following statement defines a cursor with the name psych_books, and associates it with the specified SELECT statement.
declare psych_books cursor for SELECT title, price FROM titles WHERE type = `psychology' for read only
The OPEN statement sends the cursor's SELECT statement to SQL Server to be executed. At the time the cursor is opened, result set membership and ordering are fixed. If the cursor is not INSENSITIVE, UPDATEs and DELETEs can be performed against the underlying data, and you must check the underlying data as each row is fetched to make sure the underlying data is still there.
The following is an example executes the SELECT statement defined in the previous DECLARE statement.
open psych_books
A simple FETCH statement will send the cursor's current row to the client. Once the row is returned to the client, there is nothing else you can do with the data in the row.
A typical program would repeat the FETCH statement until all the cursor rows had been read.
FETCH [[NEXT | PRIOR | FIRST | LAST| ABSOLUTE {n | @nvar} | RELATIVE {n | Â@nvar}] FROM] cursor_name [INTO @variable_name1, @variable_name2, ...]
By default, FETCH retrieves the next row from the cursor result set. If the cursor is defined with the SCROLL option, you have the following options.
The global variable @@fetch_status contains one of the following values after a FETCH.
A value of -2 for @@fetch_status is only possible if the cursor is not insensitive. It means that some other process has deleted the row or changed the key value the cursor had opened, so that the row is no longer a member of the cursor's result set.
The global variable @@fetch_status works like an EOF (end of file) marker. You must read past the last row of data to know that there are no more rows. No other statements other than FETCH affect the value in @@fetch_status.
If a fetch tries to access a row that is no longer available, the returned values will all be NULL.
A FETCH INTO can be used to place the values from the cursor row into local variables. You must have the same number of variables as columns in the cursor's SELECT statement.
The following example will place the two columns returned by the psych_books cursor into local variables.
declare @book varchar(80), @cost money fetch psych_books into @book, @cost
These variables are now available to be used until the next FETCH.
You'll look at a complete example now. This example uses the psych_books cursor previously defined, and will return each psychology book's retail price and discount price. The discount price, offered through a special promotion, depends on the retail price. Books costing less than $10 get a 10 percent discount, books costing between $10 and $20 get a 15 percent discount, and books costing more than $20 get a 20 percent discount.
This cursor is not insensitive, even though it is read only, so other processes may be modifying rows in the titles table. Thus, you need to include the check for a @@fetch_status value of -2.
declare psych_books cursor for SELECT title, price FROM titles WHERE type = `psychology' for read only declare @book varchar(80), @cost money open psych_books fetch psych_books into @book, @cost while @@fetch_status != -1 BEGIN IF @@fetch_status = -2 PRINT `This row is no longer available.' ELSE IF @cost <$10 SELECT @book, @cost, special = @cost * 0.9 ELSE IF @cost between $10 and $20 SELECT @book, @cost, special = @cost * 0.85 ELSE IF @cost > $20 SELECT @book, @cost, special = @cost * 0.8 fetch psych_books into @book, @cost END PRINT `End of list.' (1 row(s) affected) special -------------------------- -------------------------- Computer Phobic AND Non-Phobic Indivi... 21.59 17.27200 (1 row(s) affected) (1 row(s) affected) special -------------------------- -------------------------- Is Anger the Enemy? 10.95 9.307500 (1 row(s) affected) (1 row(s) affected) special -------------------------- -------------------------- Life Without Fear 7.00 6.30000 (1 row(s) affected) (1 row(s) affected) special -------------------------- -------------------------- Prolonged Data Deprivation:... 19.99 16.991500 (1 row(s) affected) (1 row(s) affected) special -------------------------- -------------------------- Emotional Security: A New... 7.99 7.19100 (1 row(s) affected) (0 row(s) affected) End of list.
A cursor is explicitly updatable when the DECLARE statement includes FOR UPDATE. A cursor is implicitly updatable if the DECLARE statement does not include any of the following:
In addition, for a cursor to be updatable all tables must have a unique index.
You can modify the row that has just been fetched with an UPDATE, or you can DELETE the row that has just been fetched. To specify the current cursor position, use WHERE CURRENT OF <cursor_name>.
After an UPDATE, the cursor's position is unchanged. After a DELETE the cursor's position is on the row after the one deleted. You cannot INSERT through a cursor.
This example will update the price of each book by an amount dependent on the original price. Books costing less than $10 get a 10 percent increase, books costing between $10 and $20 get a 15 percent increase, and books costing more than $20 get a 20 percent increase.
declare book_prices cursor for SELECT title_id, price FROM titles for update of price declare @book varchar(80), @cost moneyopen book_prices fetch book_prices into @book, @cost while @@fetch_status = 0 BEGIN IF @cost <$10 update titles SET price = @cost * 1.1 WHERE current of book_prices ELSE IF @cost between $10 and $20 update titles SET price = @cost * 1.15 WHERE current of book_prices ELSE IF @cost > $20 update titles SET price = @cost * 1.2 WHERE current of book_prices fetch book_prices into @book, @cost END CLOSE book_prices DEALLOCATE book_prices
The CLOSE statement closes an open cursor. CLOSE leaves the data structures accessible for reopening; however, modifications or fetches are not allowed until the cursor is reopened. If the same cursor is reopened, the first row of the result set becomes the current row.
The following example closes the cursor previously defined.
close psych_books
The DEALLOCATE statement removes the cursor data structures. DEALLOCATE is different from CLOSE in that a closed cursor can be re-opened, but a deallocated cursor cannot be. DEALLOCATE releases all data structures associated with the cursor and removes the definition of the cursor.
The following example deallocates the cursor previously defined above.
deallocate psych_books
Terminating a client connection will close and deallocate any cursors.
Today you learned the programming constructs that allow your SQL Server stored procedures, triggers, and batches to be very powerful.
You saw the syntax for declaring and using variables, for implementing Flow of Control, and for printing messages and errors. Finally, you examined in detail all the options for working with cursors to allow you to process data one row at a time.
SELECT @today = getdate( ) SELECT today = getdate( )
The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience using what you've learned. Try to understand the quiz and exercise answers before continuing on to the next day's lesson. Answers are provided in Appendix B.
The exercises below use the stores table located in the pubs database.
SELECT stor_name, stor_address, city, state FROM stores
stor_name stor_address city, state
stor_name out of state
SELECT stor_name, stor_address, city, state FROM new_stores
© Copyright, Macmillan Computer Publishing. All rights reserved.