SQL Stored Procedures

A Stored Procedure is a precompiled collection of Transact-SQL statements stored under a name and processed as a single execution plan.

IBuy Spy Stored Procedures The portal uses stored procedures to encapsulate all of the database queries. Stored procedures provide a clean separation between the database and the middle-tier data access layer. This in turn provides easier maintenance, since changes to the database schema will be invisible to the data access components. Using stored procedures also provide performance benefits since they are optimized the first time they are run and then retained in memory for subsequent calls.

Stored procedures are a very important feature of a relational database system. Up and till now, you've probably hard-coded SQL statements into your programs. There are then sent to to the database server, JIT compiled and executed. These have their place, for example, if you need to dynamically generate SQL (in a very complicated search system, for instance). However, if you're calling the same SQL code many times over, it's a waste of time and server resources to keep recompiling it. Instead, it would make much more sense to store it in compiled form, right? Right! That's exactly what stored procedures let you do.

Instead of storing the SQL code in your program, it is stored in functions in the Stored Procedures section of database. You create the Stored Procedures using Enterprise Manager, compile them (by pressing Save), then, when you need to execute them, you simply call the stored procedure from your code. Stored procedures can also accept parameters from your program and return parameters and result sets to your program.

/////////////// SQL Stored Procedures

Microsoft® SQL Server™ stored procedures return data in four ways:
Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
Return codes, which are always an integer value.
A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
A global cursor that can be referenced outside the stored procedure.

Benefits of using Stored Procedure

(emeka original)

1. Consistent implementation of logic: Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

2. Shield users from the details of the database tables: Stored procedures can shield users from needing to know the details of the tables in the database. If a set of stored procedures supports all of the business functions users need to perform, users never need to access the tables directly; they can just execute the stored procedures that model the business processes with which they are familiar.

An illustration of this use of stored procedures is the SQL Server system stored procedures used to insulate users from the system tables. SQL Server includes a set of system stored procedures whose names usually start with sp_. These system stored procedures support all of the administrative tasks required to run a SQL Server system. You can administer a SQL Server system using the Transact-SQL administration-related statements (such as CREATE TABLE) or the system stored procedures, and never need to directly update the system tables.

3. Improved security: Stored procedures offers the ability to set permissions on each stored procedure, as a result, the user accessing the stored procedures only needs rights to the stored procedures and not the underlying tables, thereby resulting in improved security.

4. Performance benefits: Using stored procedures provides performance benefits since they are optimized the first time they are run and then retained in memory for subsequent calls. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server.

//////////////////////////////////

Benefits of using Stored Procedures

When executing statements, calling a stored procedure on the data source (instead of directly executing or preparing a statement in the client application) can provide:

Higher performance
SQL statements are parsed and compiled when procedures are created. This overhead is then saved when the procedures are executed.

Reduced network overhead
Executing a procedure instead of sending complex queries across the network can reduce network traffic. If an ODBC application uses the ODBC { CALL } syntax to execute a stored procedure, the ODBC driver makes additional optimizations that eliminate the need to convert parameter data.

Greater consistency
If an organization’s rules are implemented in a central resource, such as a stored procedure, they can be coded, tested, and debugged once. Individual programmers can then use the tested stored procedures instead of developing their own implementations.

Greater accuracy
Because stored procedures are usually developed by experienced programmers, they tend to be more efficient and have fewer errors than code developed multiple times by programmers of varying skill levels.

Added functionality
Extended stored procedures can use C and C++ features not available in Transact-SQL statements.

///////////////////////

Creating Your First Stored Procedure

As an example, we'll create a stored procedure for the sample Northwind database. This is in fact the stored procedure used in both the Visual Basic 6 and C# .NET tutorials which explain how to call a stored procedure. First, open Enterprise Manager and expand the tree to view the objects in the Northwind database. Click the Stored Procedures node. In the right pane, right click and select New Stored Procedure. The following window will appear:

////add a screenshot here //// Change the code so that it reads as follows:

This example outlines a number of important things about SQL stored procedures. On the second line the only input parameter is defined, and as you can see, it is of the CHAR data type and has a length of 5. (Note: you only specify the size for data types which can have a variable size; for data types such as INT or BIGINT which have a fixed size trying to specify the size will generate an error.) Most other data types can be used for parameters (except table, picture, etc for obvious reasons). By default, parameters are input parameters. To specify an output parameter add the OUTPUT keyword, for example:

You can also specify a default value for parameters, so they don't have to have a value explicitly set in the calling code every time. For example:

When there are multiple parameters, they must be separated by commas, for example:

Stored procedures Example 3

The stored procedure example illustrates three ways stored procedures can return data:

1. First it issues a SELECT statement that returns a result set summarizing the order activity for the stores in the sales table.

2. Secondly, it then issues a SELECT statement that fills an output parameter.

3. Finally, it has a RETURN statement with a SELECT statement that returns an integer. Return codes are generally used to pass back error checking information. This procedure runs without errors, so it returns another value to illustrate how returned codes are filled.

To test the stored procedure

To execute the procedure

To Use the return code and output parameter

The output from running the Stored procedure

The output from running this sample is: EmployeeID SummSales ----------- -------------------------- 1 202,143.71 2 177,749.26 3 213,051.30 4 250,187.45 5 75,567.75 6 78,198.10 7 141,295.99 8 133,301.03 9 82,964.00 The size of the largest single order was: 130 The sum of the quantities ordered was: 51317 ///////////////////////////////////////

Running Stored Procedures

A stored procedure is an executable object stored in a database. Microsoft® SQL Server™ supports: - Stored procedures One or more SQL statements precompiled into a single executable procedure. - Extended stored procedures C or C++ dynamic-link libraries (DLL) written to the SQL Server Open Data Services API for extended stored procedures. The Open Data Services API extends the capabilities of stored procedures to include C or C++ code. Emeka Original - make sure to add////////////////////////////

Calling a Procedure from Query Analyser, or another Procedure

Returing an Error Code

Comments

Variables

If Statements

///////////////////////////////////////////

Creating a Stored Procedure

You can create stored procedures using the CREATE PROCEDURE Transact-SQL statement. Before creating a stored procedure, consider that: - CREATE PROCEDURE statements cannot be combined with other SQL statements in a single batch. - Permission to create stored procedures defaults to the database owner, who can transfer it to other users. - Stored procedures are database objects, and their names must follow the rules for identifiers. - You can create a stored procedure only in the current database. //When creating a stored procedure, you should specify: - Any input parameters and output parameters to the calling procedure or batch. - The programming statements that perform operations in the database, including calling other procedures. - The status value returned to the calling procedure or batch to indicate success or failure (and the reason for failure).

System Stored Procedures

Many of your administrative activities in Microsoft® SQL Server™ are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for stored procedures beginning with sp_ in this order: 1- Look for the stored procedure in the master database first. 2- Look for the stored procedure based on any qualifiers provided (database name or owner). 3- Look for the stored procedure using dbo as the owner, if one is not specified. Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name. -------------------------------------------------------------------------------- Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed. -------------------------------------------------------------------------------- //Grouping A procedure can be created with the same name as an existing stored procedure if it is given a different identification number, which allows the procedures to be grouped logically. Grouping procedures with the same name allows them to be deleted at the same time. Procedures used in the same application are often grouped this way. For example, the procedures used with the my_app application might be named my_proc;1, my_proc;2, and so on. Deleting my_proc deletes the entire group. After procedures have been grouped, individual procedures within the group cannot be deleted. Temporary Stored Procedures Private and global temporary stored procedures, analogous to temporary tables, can be created with the # and ## prefixes added to the procedure name. # denotes a private temporary stored procedure; ## denotes a global temporary stored procedure. These procedures do not exist after SQL Server is shut down. Temporary stored procedures are useful when connecting to earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches. Applications connecting to SQL Server version 7.0 should use the sp_executesql system stored procedure instead of temporary stored procedures. For more information, see Execution Plan Caching and Reuse. Only the connection that created a private temporary procedure can execute it, and the procedure is automatically deleted when the connection is closed (when the user logs out of SQL Server). Any connection can execute a global temporary stored procedure. A global temporary stored procedure exists until the connection used by the user who created the procedure is closed and any currently executing versions of the procedure by any other connections are completed. Once the connection that was used to create the procedure is closed, no further execution of the global temporary stored procedure is allowed. Only those connections that have already started executing the stored procedure are allowed to complete. If a stored procedure not prefixed with # or ## is created directly in the tempdb database, the stored procedure is automatically deleted when SQL Server is shut down because tempdb is re-created every time SQL Server is started. Procedures created directly in tempdb exist even after the creating connection is terminated. As with any other object, permissions to execute the temporary stored procedure can be granted, denied, and revoked to other users. //////////////////////////////////////////

Modifying and Renaming a Stored Procedure

If you need to change the statements or parameters in a stored procedure, you can either delete and re-create the stored procedure or alter the stored procedure in a single step. When you delete and re-create a stored procedure, all permissions associated with the stored procedure are lost. When you alter the stored procedure, the procedure or parameter definition is changed but the permissions defined for the stored procedure are retained. You can also rename a stored procedure. The new name must follow the rules for identifiers. You can rename only the stored procedures that you own, but the database owner can change the name of any user’s stored procedure. The stored procedure to be renamed must be in the current database. A stored procedure can also be modified to encrypt the definition or cause the procedure to be recompiled each time it is executed. ///////////////////////////////////////////

Viewing a Stored Procedure

Several system stored procedures provide information from the system tables about stored procedures. You can: - See the Transact-SQL statements used to create a stored procedure. This can be useful if you do not have the Transact-SQL script files used to create the stored procedure. - Get information about a stored procedure such as its owner, when it was created, and its parameters. - List the objects used by the specified stored procedure, and the procedures that use the specified stored procedure. This information can be used to identify the procedures affected by the changing or removal of an object in the database. //////////////////////////////////////////

Executing a Stored Procedure

Executing a stored procedure is similar to executing a prepared statement, except that the stored procedure exists as a permanently compiled object in the database. A stored procedure can also be used to hide complex SQL statements from the application. When executing a stored procedure in a Command object, the CommandType property must be specified with the adCmdStoredProc value. With adCmdStoredProc, the corresponding SQL statement for the underlining provider is generated. With MSDASQL, the ODBC escape sequences for procedure calls are generated; the Microsoft® SQL Server™ ODBC driver is optimized to take advantage of these sequences. There is no need to prepare a statement that only calls a stored procedure. Both stored procedures and prepared statements are methods of precompiling statements. Because a stored procedure is already precompiled, preparing a stored procedure call actually adds overhead. The prepared statement adds a small precompiled execution plan that calls the stored procedure execution plan, rather than executing the stored procedure execution plan directly. This example shows the execution of the sp_who SQL Server system stored procedure: Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset cn.Provider = "sqloledb" cn.Properties("Data Source").Value = "MyServerName" cn.Properties("Initial Catalog").Value = "pubs" cn.Properties("Integrated Security").Value = "SSPI" cn.Open Cmd.ActiveConnection = cn Cmd.CommandText = "sp_who" Cmd.CommandType = adCmdStoredProc Set rs = Cmd.Execute Debug.Print rs(0) rs.Close ///////////////////////////////////////////

Parameters in Stored procedures

Parameters are used to exchange data between stored procedures and whatever called the stored procedure: - Input parameters allow the caller to pass a data value to the stored procedure. - Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller. - Every stored procedure returns an integer return code to the caller. If the stored procedure does not explicitly set a value for the return code, the return code is 0. This sample stored procedure shows the use of an input parameter, an output parameter, and a return code: USE Northwind GO -- Create a procedure that takes one input parameter -- and returns one output parameter and a return code. CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT, @MaxQuantity INT OUTPUT AS -- Declare and initialize a variable to hold @@ERROR. DECLARE @ErrorSave INT SET @ErrorSave = 0 -- Do a SELECT using the input parameter. SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = @EmployeeIDParm -- Save any nonzero @@ERROR value. IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR -- Set a value in the output parameter. SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details] IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR -- Returns 0 if neither SELECT statement had -- an error; otherwise, returns the last error. RETURN @ErrorSave GO When a stored procedure is executed, input parameters can either have their value set to a constant or use the value of a variable. Output parameters and return codes must return their values into a variable. Parameters and return codes can exchange data values with either Transact-SQL variables or application variables. If a stored procedure is called from a batch or script, the parameters and return code values can use Transact-SQL variables defined in the same batch. This example is a batch that executes the procedure created earlier. The input parameter is specified as a constant and the output parameter and return code place their values in Transact-SQL variables: -- Declare the variables for the return code and output parameter. DECLARE @ReturnCode INT DECLARE @MaxQtyVariable INT -- Execute the stored procedure and specify which variables -- are to receive the output parameter and return code values. EXEC @ReturnCode = SampleProcedure @EmployeeIDParm = 9, @MaxQuantity = @MaxQtyVariable OUTPUT -- Show the values returned. PRINT ' ' PRINT 'Return code = ' + CAST(@ReturnCode AS CHAR(10)) PRINT 'Maximum Quantity = ' + CAST(@MaxQtyVariable AS CHAR(10)) GO An application can use parameter markers bound to program variables to exchange data between application variables, parameters, and return codes. ///////////////////New Copy Code //////////////////////////

Related Content