Leveraging SQL Server 7.0 Stored Procedures

By Sanjiv Purba

Introduction

Last week I introduced you to stored procedures, database objects that the American National Standards Institute defines as part of the Transact-SQL extension. If you missed part one of this article, I suggest reading it over before you move forward. If you’re already familiar with stored procedures and get busy using them, let’s get started!

Creating and Dropping Stored Procedures

Stored procedures are database objects that include the name of the stored procedure, input parameters, local variables, control-of-flow statements, DML statements, DDL statements, some DCL statements, global variables and a return statement. Stored procedures are created with the CREATE PROCedure statement. A stored procedure must be dropped with the DROP PROCedure statement before it is re-created.

The syntax to create a stored procedure is as follows:

	USE database_name
CREATE PROCedure [owner.] name
[@input_parm_list   dtype   =default     OUTput]
[options]
AS
     Transact SQL Statements
GO

The syntax to remove a stored procedure from a database is as follows:

	USE database_name
DROP PROCEDURE proc_name
GO

Executing Stored Procedures

Stored procedures can be executed using the following syntax:

EXECute proc_name [optional_parameter_list]

This command can be executed at the SQL Server command prompt, inside the Query Analyzer, from inside another stored procedure, or from a client.

User-Defined Variables

Variables are defined in stored procedures using a DECLARE statement, and a mneumonic variable name that is prefaced with ‘@’. Some examples of this are as follows:

DECLARE @last_user_name        	char(30),
        	@first_user_name      char(30),
        	@years_of_experience   int,
        @promotion_date        	datetime

Global Variables

Global variables can also be inspected inside stored procedures, for example, with the SELECT or PRINT statements. Commonly used global variables are @@VERSION, @@CONNECTIONS, @@ERROR, @@FETCH_STATUS, @@IDENTITY, @@NESTLEVEL, @@ROWCOUNT, @@SERVERNAME, @@TOTAL_ERRORS, and @@TRANCOUNT.

Control-of-Flow Language

The control-of-flow extensions that are available in SQL Server to build sophisticated stored procedures include the following statements: BEGIN..END, IF..ELSE, IF EXISTS, CASE ..END, WAITFOR, GOTO LABEL [label:], COMPUTE, WHILE, CONTINUE, BREAK, and RETURN. These are described in more detail in this section.

BEGIN..END

This command block is used to surround multiple Transact-SQL statements in stored procedures so that they are executed as a single statement. The syntax for this command is as follows:

BEGIN
Statements
Statements
Statements
END

IF..ELSE

The IF..ELSE command block supported in the Control-of-flow dialect is the classical third-generation if-else construct. When the condition belonging to the IF evaluates to true, the statement immediately following the IF statement is executed and the statement belonging to the ELSE is skipped. If the condition belonging to the IF is false, then the statement corresponding to the ELSE is executed. IF..ELSE constructs can also be nested. The syntax for this command block is as follows:

IF Condition
     Statements
ELSE
IF Condition
     Statements
ELSE
IF condition
     Statements
ELSE
     Statements

IF EXISTS

The IF EXISTS test is used to determine if a specific object exists within a database. The statement can be used to inspect the sysobjects sytem table that contains a row for every object in the database. An example for doing this is as follows:

IF EXISTS 
(select name from sysobjects where 
name = 'value_of_name' 
AND type = 'INITIAL')
drop table table_name
go

CASE..END

In this statement, a condition is evaluated at the start of the command block and a branch that matches the condition is then executed. Statements belonging to other conditions are skipped. Control is passed to the statement immediately following the CASE..END block after the statement is executed. The syntax for this command is as follows:

CASE condition
     WHEN condition THEN statements
     WHEN condition THEN statements
     WHEN condition THEN statements
     ELSE statements
END

GOTO LABEL [label:]

The first component of this construct consists of the GOTO LABEL code that forces an unconditional branch to the LABEL. The second component of this construct is a label: that marks a spot somewhere in the same stored procedure batch. Use of a GOTO is always controversial as a programming technique. It should be used consistently and clearly to avoid countless or untraceable branches. The syntax for this command is as follows:

GOTO label_name

label_name:

WAITFOR

The WAITFOR statement pauses active processing of the batch until a specific statement is true. This command is generally used in conjunction with a timer that stops the processing for a specified time increment before continuing. The syntax for this command is as follows:

WAITFOR DELAY'time_meter' | TIME 'specific_time'

COMPUTE

The COMPUTE statement is used to calculate results and save them in a declared variable. The following example demonstrates use of the COMPUTE statement:

COMPUTE salary = base + bonus 

WHILE

The WHILE statement is used to execute a set of statements until a condition is met. The condition can be a compound statement that combines multiple conditions using AND and OR. The syntax for this command is as follows:

WHILE condition
BEGIN
Statements
END

CONTINUE

The CONTINUE statement is used to pass control to the start of a WHILE statement where the condition is evaluated again. The syntax for this command is the word itself:

CONTINUE

BREAK The BREAK statement is used to immediately and unconditionally exit a WHILE statement block. Control is passed to the statement immediately following the WHILE block. The syntax for this command is the word itself:

BREAK

RETURN

The RETURN statement is used to send a status back to the calling program and exit from the current program (e.g. RETURN 0).

GENERATING ERRORS

The Raiserror statement generates an error message from a stored procedure. An example of this is as follows:

raiserror 60200 'testing raiserror', 3
select @@error

or

sp_addmessage 60200, 11, 'this is a message'
raiserror (60200, 11, 1)

ARITHMETIC AND BOOLEAN OPERATORS

SQL Server supports the common arithmetic operators that most other development languages do, including + (addition), – (subtraction), / (division), * (multiplication), and % (modulo). A large set of boolean operators can be used in stored procedures, including > (greater than), = (greater than or equal to), (not equal to), and = (equal to). These can be used in stored procedures as well.

Conclusion

Stored procedures have been critical to the growing popularity of SQL Server since 1987. Despite the availability of other techniques, stored procedures continue to be popular in application architecture due to their power, performance boosts, security augmentation, and support for n-tier architecture. Stored procedures support sophisticated language constructs including a rich control-of-flow language, DDL statements, DML statements, DCL statements, global variables, and functions.

About the author:

Sanjiv Purba is a Senior Manager with Deloitte Consulting. He is the author of five books published by John Wiley, the most recent of which is Building Microsoft SQL Server 7 Applications with COM.