Examples

Note Most examples refer to the pubs sample database. The pubs sample database is included as a learning tool; it is created when the server is installed. To use the sample database, your system must have access to the pubs database. If you have a localized version of SQL Server and want to try the examples, drop the localized version of pubs and install the U.S. English version of pubs. To install the U.S. English version of pubs, run the INSTPUBS.SQL script with the isql command-line utility. This script can be found in the INSTALL directory of SQL Server. Also, if other users have executed the examples in this chapter, it is likely that the pubs database has been altered. This will change the output you see from some of the examples. You can reinstall the original pubs database at any time by running the INSTPUBS.SQL script.

To run the INSTPUBS.SQL script, from an operating-system prompt, type:

isql /Usa /Ppassword /Sserver -i\sql60\install\instpubs.sql

For details about isql, see the isql command-line utility in the Microsoft SQL Server Transact-SQL Reference.

Here is a stored procedure that is useful in the pubs database. Given an author's last and first name, it displays the title and publisher of each of that author's books:

CREATE PROC au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name
FROM authors, titles, publishers, titleauthor
WHERE au_fname = @firstname
AND au_lname = @lastname
AND authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
AND titles.pub_id = publishers.pub_id

You get a message stating that the command did not return any data, and it did not return any rows. This is fine. The procedure has been created.

Now execute au_info:

au_info Ringer, Anne

au_lname	au_fname	title	pub_name
---------	---------	---------------------	----------------
Ringer	Anne	The Gourmet Microwave	Binnet & Hardley
Ringer	Anne	Is Anger the Enemy?	New Moon Books

(2 row(s) affected)

Note If you supply parameters in the form @parameter = value, you can supply them in any order. You can also omit parameters for which defaults have been supplied. If you supply one parameter in the form @parameter = value, you must supply all subsequent parameters this way. If you do not supply parameters in the form @parameter = value, you must supply them in the order given in the CREATE PROCEDURE statement.

When executing a stored procedure, the server will reject any parameters that were not included with the parameter list during procedure creation. Any parameter that is passed by reference (explicitly passing the parameter name) will not be accepted if the parameter name does not match. (For details on creating procedures and passing parameters, see the CREATE PROCEDURE and EXECUTE statements in the Microsoft SQL Server Transact-SQL Reference.)

Although you can omit parameters for which defaults have been supplied, you cannot skip any. In other words, if a procedure has five parameters, you can omit both the fourth and the fifth parameters, but you cannot skip the fourth and still include the fifth.

You can assign a default value for the parameter in the CREATE PROCEDURE statement. This value, which can be any constant, is taken as the parameter to the procedure when the user does not supply one.

Here is a procedure that displays the names of all authors who have written a book published by the publisher given as a parameter. If no publisher name is supplied, the procedure shows the authors published by Algodata Infosystems.

CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'
AS SELECT au_lname, au_fname, pub_name
FROM authors a, publishers p, titles t, titleauthor ta
WHERE @pubname = p.pub_name
AND a.au_id = ta.au_id
AND t.title_id = ta.title_id
AND t.pub_id = p.pub_id

Note that if the default value is a character string that contains embedded blanks or punctuation, or if it begins with a number (for example, 6xxx), it must be enclosed in single quotation marks.

Here's the output from executing pub_info2 with no parameter specified:

pub_info

au_lname	au_fname	pub_name
--------------	----------------	--------------------
Green	Marjorie	Algodata Infosystems
Bennet	Abraham	Algodata Infosystems
O'Leary	Michael	Algodata Infosystems
MacFeather	Stearns	Algodata Infosystems
Straight	Dean	Algodata Infosystems
Carson	Cheryl	Algodata Infosystems
Dull	Ann	Algodata Infosystems
Hunter	Sheryl	Algodata Infosystems
Locksley	Charlene	Algodata Infosystems

(9 row(s) affected)

In the following stored procedure, showind2, titles is assigned as the default value for the @table parameter:

CREATE PROC showind2 @table varchar(30) = titles
AS SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes, sysobjects
WHERE sysobjects.name = @table
AND sysobjects.id = sysindexes.id

The column headings (for example, TABLE_NAME) make the results more readable. Here's what the procedure shows for the authors table:

showind2 authors

TABLE_NAME	INDEX_NAME	INDEX_ID
----------	----------	----------
authors	UPKCL_auidind	1
authors	aunmind	2

(2 row(s) affected)

If the user does not supply a value, SQL Server uses the default table, titles:

showind2

TABLE_NAME	INDEX_NAME	INDEX_ID
----------	----------	----------
titles	UPKCL_titleidind	1
titles	titleind	2

(2 row(s) affected)

If a parameter is expected but none is supplied, and if a default value is not supplied in the CREATE PROCEDURE statement, SQL Server displays an error message that lists the expected parameters.

The default can be the value NULL. In this case, if the user does not supply a parameter, SQL Server executes the stored procedure according to its other statements. No error message is displayed.

The procedure definition can also specify that some other action be taken if the user does not give a parameter. Here's an example:

CREATE PROC showind3 @table varchar(30) = NULL
AS IF @table IS NULL
    PRINT 'Please give a table name'
ELSE
    SELECT TABLE_NAME = sysobjects.name,
    INDEX_NAME = sysindexes.name, INDEX_ID = indid
    FROM sysindexes, sysobjects
    WHERE sysobjects.name = @table
    AND sysobjects.id = sysindexes.id

When the user fails to give a parameter, SQL Server displays the user-supplied message from the procedure.

(For other examples of setting the default to NULL, examine the text of some system procedures using sp_helptext.)

The default can include wildcard characters (%, _, [] and [^]) if the procedure uses the parameter with the LIKE keyword.

For example, showind can be modified to display information about the system tables if the user does not supply a parameter:

CREATE PROC showind4 @table varchar(30) = 'sys%'
AS SELECT TABLE_NAME = sysobjects.name,
    INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes, sysobjects
WHERE sysobjects.name like @table
    AND sysobjects.id = sysindexes.id

The following variation of the stored procedure au_info has defaults with wildcard characters for both parameters:

CREATE PROC au_info2 @lastname varchar(30) = 'D%',
    @firstname varchar(18) = '%'
AS SELECT au_lname, au_fname, title, pub_name
FROM authors, titles, publishers, titleauthor
WHERE au_fname LIKE @firstname
    AND au_lname LIKE @lastname
    AND authors.au_id = titleauthor.au_id
    AND titles.title_id = titleauthor.title_id
    AND titles.pub_id = publishers.pub_id

If au_info2 is executed with no parameters, all the authors with last names beginning with the letter D are displayed:

au_info2

au_lname	au_fname	title	pub_name
--------	--------	---------------------	-------------------
Dull	Ann	Secrets of Silicon Val	Algodata Infosystems
del Castillo	Innes	Silicon Val Gastrono	Binnet & Hardley
DeFrance	Michel	The Gourmet Microwave	Binnet & Hardley

(3 row(s) affected)

This example omits the second parameter when defaults for two parameters have been defined, so you can find the books and publishers for all authors with the last name Ringer:

au_info2 Ringer

au_lname	au_fname	title	pub_name
---------	---------	-------------------	---------------
Ringer	Anne	The Gourmet Microwave	Binnet & Hardley
Ringer	Anne	Is Anger the Enemy?	New Moon Books
Ringer	Albert	Is Anger the Enemy?	New Moon Books
Ringer	Albert	Life Without Fear	New Moon Books

(4 row(s) affected)