| June 11, 1999
Leveraging SQL Server 7.0 stored procedures
By Sanjiv Purba
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:
The syntax to remove a stored procedure from a database is as follows:
Executing Stored Procedures
Stored procedures can be executed using the following syntax:
This command can be executed at the SQL Server command prompt, inside the Query Analyzer, from inside another stored procedure, or from a client.
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:
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.
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.
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:
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:
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:
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:
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:
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:
The COMPUTE statement is used to calculate results and save them in a declared variable. The following example demonstrates use of the COMPUTE statement:
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:
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:
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:
The RETURN statement is used to send a status back to the calling program and exit from the current program (e.g. RETURN 0).
The Raiserror statement generates an error message from a stored procedure. An example of this is as follows:
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.
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.