Introducing SQL Server 7.0 stored procedures

Introduction

SQL Server stored procedures are essential structures for supporting client/server and n-tier distributed applications. These are essentially application modules or programs that are saved inside a database server’s system tables. They can be invoked by other clients or application tiers and are generally processed on the same server platform that supports the database server.

Stored procedures are database objects that are defined in the American National Standards Institute ANSI/92 standard as part of the Transact-SQL extension. Other objects defined in this extension include triggers, views, cursors, datatypes, and user-defined datatypes. Stored procedures support the SQL data definition language (DDL), SQL data manipulation language (DML), SQL data control language (DCL), and control-of-flow statements.

The Basic Process

Stored procedures are created and saved under unique names within system tables in a database. The essential process for manipulating stored procedures is to first use a text editor or a wizard to write the code of the stored procedure. Then drop the stored procedure from a specific database if it already exits. Next, compile the stored procedure into the system tables of a specific database. Then fix any compile errors and recompile the stored procedure until it compiles successfully. Successful compiles store the procedure text into several system tables (including sysobjects and syscomments) and also builds a cost-based execution plan that is saved into the data dictionary. Finally, stored procedures are executed by name with an optional parameter list.

Design Philosophy and n-tier architecture

There is a great deal of flexibility in building the contents of stored procedures. Since they can contain a combination of DDL, DML, DCL and Transact-SQL statements, stored procedures can support the same functionality as modules written in third generation language.

Traditional client/server architecture provides a fair degree of freedom in stored procedure design that can range from simple (affecting a single table) to highly complex (including validation code and support for business processes). N-tier architecture specifically relies on an additional application layer that supports stateless objects. Stored procedures that are simplified and atomic are better suited to this type of architecture. At the physical level, this layer can be implemented using the Microsoft Transaction Server (MTS).

Common atomic level stored procedures complete a single operation against a table, such as selecting data rows from a table or view (SELECT), inserting data rows into a table or view (INSERT), deleting data rows from a table or view (DELETE), and updating columns in a table or view (UPDATE).

Benefits

The essential benefits of stored procedures focus on application performance, security, encapsulation, and change management. Use of stored procedures in an application can dramatically improve performance. This can happen for several reasons. A reduced volume of data is sent across a network in terms of command statements and result sets. Stored procedures are precompiled, and can be executed faster than the alternative method of issuing dynamic SQL statements.

Stored procedures are also commonly used to manage security. Permissions can be established for stored procedures to limit user access to the database. Since stored procedures are saved inside SQL Server, its code is maintained inside a corporate firewall. The logic is protected by all the security measures available to protect the platform server.

Version Control of Stored Procedure Code

Stored procedures should be created using text script files. It is highly recommended to maintain script files after the stored procedures are successfully compiled into the database server. This is to ensure that previous versions of a stored procedure can be recreated in the future. The database server only retains the current successfully compiled version. The script files should be managed within a change management/version control process that is applied to other application code.

SQL Server 7 supplies a ‘create stored procedures’ wizard that offers a quick way to build three basic types of stored procedures for table processing, including insert, delete, and update table operations. It is recommended to save text script files separately after the stored procedures are generated using this wizard.

Now that you have the introduction of leveraging SQL Server 7.0 stored procedures be sure to check back next week as Sanjiv Purba explains how to create and execute stored procedures.

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.