Converting Constructs to Code

Both stored procedures and user-defined functions allow you to pass and return parameters. To control what stored procedures and functions do, different values can be passed that control how they work. In addition, parameters provide a way to retrieve the data found by the execution.

Using Stored Procedures

Stored procedures are the mainstay of application programming with SQL Server. Typically, applications that perform well and that are easily managed employ stored procedures exclusively for their data retrieval and update needs. This helps facilitate centralized management of queries, the capability to change data models and tune queries without rewriting code, and the capability to manage transactions without user intervention.

Playing the role of the middle tiers in n-tier architecture, stored procedures, and user-defined functions provides facilities for validating and modifying data to conform to business rules.

Using stored procedures is a powerful and flexible technique for performing tasks within an application. A stored procedure, when it is first used, is compiled into an execution plan that remains in the procedure cache. This provides for some of the performance over ad-hoc operations. The performance improvements in SQL 7 and 2000 are not as drastic as in previous versions because changes in the way that other operations now execute provide them with some of the same benefits as stored procedures. A stored procedure can accept parameters, process operations against any number of databases, and return results to the calling process.

A stored procedure, like other operations, can be encrypted to protect the details of the operation. An application might need to send several operations across a network and respond conditionally to the results. This can be handled with a single call if the logic is contained in a single stored procedure. The use of local and global cursors can expose information to the application or other applications as needed, giving provisions for complex development processes with conversations between separate processes.

A stored procedure is any set of T-SQL statements compiled into a single execution plan and stored within the database for future execution. When a stored procedure is compiled into an execution plan, it is compiled once and stored into RAM, where the execution plan may be reused by the server. This presents a slight improvement in performance over noncached coding implementations.

Temporary stored procedures used frequently in earlier versions are still supported by SQL Server, although improvements in other areas should eliminate or reduce the need for their use. The most significant improvement is the capability to compile and maintain most SQL operations in cache for prolonged periods.

A stored procedure can have input and output parameters. A stored procedure also has an integer return value that can be used to indicate success or failure or can be used as an alternative output. This means that a stored procedure can return results in several ways: with an integer return code, with one or more resultsets, and with output parameters.

The T-SQL CREATE PROCEDURE statement is used to create a stored procedure. You can execute this statement from the Query Analyzer, or you can access it through the Enterprise Manager by right-clicking on Stored Procedures under the database and choosing the New Stored Procedure option. The procedure is then saved within the current database as an object.

Creating Stored Procedures

Stored procedures are created using the CREATE PROCEDURE statement. The CREATE PROCEDURE statement has to be the first executable line in a batch, and the stored procedure continues until the end of the batch, which is until the end of the file or until the word GO. You can abbreviate the word PROCEDURE to just PROC.

Stored procedures are used as a way to perform complex activities, and they can be used to hide the details of a process for security reasons. In this respect, stored procedures share similar qualities as views, providing the necessary results without the need for the knowledge of the underlying objects. If a user has permission to execute a procedure, he can perform activities not normally covered by his own permissions. A user who is not permitted to delete or insert data could do so through the execution of a procedure that performs these activities on behalf of the user. This may seem like a breach of security, but remember that the procedure only does exactly as it's told and removes data only as instructed in a controlled manner.

A simple procedure could be nothing more than a saved query. In the following example a procedure is created that will list all tables in the current database.

 CREATE PROCEDURE TableList AS SELECT name FROM sysobjects WHERE xtype = 'u' GO 

The procedure returns the same thing all the time: the list of tables that include the objects of type 'u' in the database in which the stored procedure was created. An improvement in the procedure to allow for more flexibility could possibly allow a parameter to be passed in so that any object types could be listed. A better name should also then be chosen, as in the following:

 CREATE PROCEDURE ObjectList    @ObjectType varchar(2)  AS SELECT name FROM sysobjects WHERE xtype = @ObjectType GO 

This procedure makes use of a parameter, supplied to allow for input to the procedure. A parameter is a variable that is passed in to a stored procedure to give the stored procedure a little guidance on how to get things done. When you use parameters in this manner, the finished product is a far more usable and flexible stored procedure. One problem exists, however: If a parameter is not provided, you end up with an error. For this reason, in many circumstances you will want to provide parameter defaults within the procedure so that the parameter itself becomes optional. Many programming languages have implementations for optional parameters, and in T-SQL it is simply a parameter with a default value supplied, as in the following completed version of the process:

 CREATE PROCEDURE ObjectList    @ObjectType varchar(2) = 'u'  AS SELECT name FROM sysobjects WHERE xtype = @ObjectType GO 

Look at how this procedure can now be used. Using this single procedure, we can list primary keys using a type 'PK' or system tables using a type 'S'. When no parameter is passed into the procedure, the default, a type 'u', will be utilized by the process.

Parameters are commonplace within stored procedures. Input parameters within our example allow for flexibility in the use of a procedure, but you may also desire a procedure that provides feedback in the form of parameter information passed out of the procedure. Output parameters are one of three mechanisms for obtaining output from a procedure.

Results from Procedure Execution

There are essentially two types of procedures needed by most systems. A common type of procedure is one that performs a necessary process. In many cases there is no need for much output from the process. The purpose of the process is to perform the task and that is all. The second type of procedure is one that provides output. Anything from a simple query, complex joining of data, or another form of feedback is needed by the process to provide information from the system.

The most common form of output provided by a stored procedure is a recordset, a set of data in the form of columns and rows to provide information as an end result of the procedure. The results of a query or complex logical process that provides the recordset define the makeup of the procedure but represent only one form of data that can be returned. When a stored procedure is executed, a separate recordset is provided as the output for each SELECT.

Another common form of output is a return value indicating the success, failure, or other status of the procedure. A return value is always an integer value and is passed from the procedure upon completion using a RETURN statement. A common use of a return value is to indicate the result of a procedure execution. A zero returned from the procedure would indicate successful execution; a negative value could indicate an error; a positive value could indicate a choice made. Return values have somewhat limited usage because they can be only of an integer data type.

Other possible outputs from a procedure include messages from the system such as how many records were processed, printed output resulting from the execution of a PRINT statement, and error messages provided when problems exist in the code or in the data.

True Customization with User-Defined Functions

User-defined functions, a new feature for SQL Server 2000, provide further encapsulation of logic into highly reusable and efficient components. In some applications, the functions available from the SQL Server installation do not suit all needs. It is for these instances that user-defined functions were intended. The functions can contain any combination of T-SQL statements. These functions act similarly to stored procedures with the exception that any errors occurring inside the function cause the entire function to fail.

SQL Server supports three varieties of user-defined functions:

• Scalar functions

• Inline table-valued function

• Multi-statement table-valued functions

The functions defined can accept parameters if needed and return either a scalar value or a table. A function cannot change any information outside the scope of the function and therefore maintains no information when processing has been completed. Other activities that are not permitted include returning information to the user and sending email. The CREATE FUNCTION statement is used to define a user-defined function similar to the following:

 CREATE FUNCTION MyFunction (@Num1 smallint, @Num2 smallint)         RETURNS real AS    BEGIN    Declare @ReturnValue real              If (@Num1 > @Num2)               Set @ReturnValue = @Num1 * 2 + 30              If (@Num1 = @Num2)               Set @ReturnValue = @Num1 * 1.5 + 30              If (@Num1 < @Num2)               Set @ReturnValue = @Num1 * 1.25 + 30              If (@Num1 < 0)               Set @ReturnValue = @Num2 * 1.15 + 30              Return(@ReturnValue)    END 

You will definitely see user-defined functions on the exam. The coding is similar to that of stored procedures. The short description given here does not indicate the lack of exam coverage for this topic. It simply shows that it would be redundant to go through all the information presented in the preceding section again.

User-defined functions (UDFs) represent powerful functionality that has a wide variety of uses within the SQL Server environment.