Converting Constructs to Code
Both stored procedures and
Using Stored Procedures
Stored procedures are the mainstay of application programming with SQL Server. Typically, applications that perform well and that are easily managed
Playing the role of the middle tiers in n-
Using stored procedures is a powerful and flexible technique for performing
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
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
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
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
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
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
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
Results from Procedure Execution
The most common form of output provided by a stored procedure is a recordset, a set of data in the form of
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
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
SQL Server supports three varieties of user-defined 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
User-defined functions (UDFs) represent powerful functionality that has a wide variety of uses within the SQL Server environment.