Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 17

Programming SQL Server (Advanced Topics)


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.

Variables

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.

Local Variables

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

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.

Control of Flow

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.

BEGIN...END Block

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.

PRINT Statement

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.

IF...ELSE Block

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.


CASE Expression

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.

Example of a Simple CASE Expression

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.

COALESCE Function

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.

Table 17.1. Sample data in the wages table.

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

NULLIF Function

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).

Table 17.2. Sample data in the wages table.

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
You need to write a single SELECT statement that will return how many employees receive an hourly wage, how many receive a salary, and how many work on commission. The following will NOT work.

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

WHILE Statement

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.


EXECUTE Statement

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.

RAISERROR Statement

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:

Cursors

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.

DECLARE

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:

Syntax for the DECLARE CURSOR Statement

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

OPEN

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

FETCH

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.

Syntax for the FETCH Statement

FETCH [[NEXT | PRIOR | FIRST | LAST| ABSOLUTE {n | @nvar} | RELATIVE {n | Â@nvar}]
FROM] cursor_name
[INTO @variable_name1, @variable_name2, ...]

FETCHing from a SCROLL Cursor

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.

Checking the Cursor's Status

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.

Processing the Cursor's Rows

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.

Modifying the Current Cursor Row

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

CLOSE

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

DEALLOCATE

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.

Summary

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.

Q&A

Q What is the difference between these two SELECT statements?
SELECT @today = getdate( )
SELECT today = getdate( )
A The first SELECT is an assignment SELECT statement, which assigns a value to the local variable @today. This variable must have been declared previously within the same batch. The statement will not return any data. The second SELECT is a normal SELECT that returns data to the client; it will return today's date, with the word today as the column header.

Q How can I use a CASE statement to execute one of several different procedures depending on the value in a local variable?


A
You cannot use the CASE construct in this way. The CASE construct can be used in place of an expression, not a statement. You can use the CASE expression to update a column to a different value depending on the value of a local variable. You can use an IF statement to execute one of several different procedures depending on the value in a local variable.

Q Is there any performance penalty for using cursors?


A
The performance overhead of having to process rows one-at-a-time is enormous compared to the 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.

Workshop

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.

Quiz

1. True or false: I would use a WHILE statement to update every row in a table, for example, to double the price of every book.

2. How do I assign a value to a local variable?

3. What is the scope of a local variable?

4. How do I assign a value to a global variable?

5. What kinds of modifications can you do through a cursor?

Exercises

The exercises below use the stores table located in the pubs database.

1. Declare a read-only cursor for the following SELECT statement.
SELECT stor_name, stor_address, city, state
FROM stores
2. Print the address of each store located in Washington state in the following format.
stor_name
stor_address
city, state
Print the address of each store not located in Washington state in the following format.
stor_name
out of state
3. Make a copy of your stores table by using SELECT INTO, and build a unique index on the stor_id column of the new table.

4. Declare an updatable cursor for the following SELECT statement.
SELECT stor_name,  stor_address, city, state
FROM new_stores
5. Using the cursor you just defined, update the city and state of each row to be null. Update the stor_address column according to the following criteria:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.