Stored Procedures , Creating and Executing

A stored procedure is a special kind of batch written in Transact-SQL, using the SQL language and its procedural extensions. The main difference between a batch and a stored procedure is that the latter is stored as a database object. In other words, stored procedures are saved on the server side to improve the performance and consistency of repetitive tasks.

The Database Engine supports stored procedures and system procedures. Stored procedures are created in the same way as all other database objects—that is, by using the DDL. System procedures are provided with the Database Engine and can be used to access and modify the information in the system catalog. This section describes (user-defined) stored procedures, while system procedures are explained in the next chapter.

When a stored procedure is created, an optional list of parameters can be defined. The procedure accepts the corresponding arguments each time it is invoked. Stored procedures can optionally return a value, which displays the user-defined information or, in the case of an error, the corresponding error message.

A stored procedure is precompiled before it is stored as an object in the database. The precompiled form is stored in the database and used whenever the stored procedure is executed. This property of stored procedures offers an important benefit: the repeated compilation of a procedure is (almost always) eliminated, and the execution performance is therefore increased. This property of stored procedures offers another benefit concerning the volume of data that must be sent to and from the database system. It might take less than 50 bytes to call a stored procedure containing several thousand bytes of statements. The accumulated effect of this savings when multiple users are performing repetitive tasks can be quite significant.

Stored procedures can also be used for the following purposes:

• To control access authorization
• To create an audit trail of activities in database tables

The use of stored procedures provides security control above and beyond the use of the GRANT and REVOKE statements, which define different access privileges for a user. This is because the authorization to execute a stored procedure is independent of the authorization to modify the objects that the stored procedure contains, as described in the next section.

Stored procedures that audit write and/or read operations concerning a table are an additional security feature of the database. With the use of such procedures, the database administrator can track modifications made by users or application programs.

Creation and Execution of Stored Procedures

Stored procedures are created with the CREATE PROCEDURE statement, which has the following syntax:

CREATE PROC[EDURE] [schema_name.]proc_name
[({@param1} type1 [ VARYING] [= default1] [OUTPUT])] {, …}
[WITH {RECOMPILE | ENCRYPTION | EXECUTE AS 'user_name'}]
[FOR REPLICATION]
AS batch | EXTERNAL NAME method_nameCode language: PHP (php)

schema_name is the name of the schema to which the ownership of the created stored procedure is assigned. proc_name is the name of the new stored procedure. @param1 is a parameter, while type1 specifies its data type. The parameter in a stored procedure has the same logical meaning as the local variable for a batch. Parameters are values passed from the caller of the stored procedure and are used within the stored procedure. default1 specifies the optional default value of the corresponding parameter. (Default can also be NULL.)

The OUTPUT option indicates that the parameter is a return parameter and can be returned to the calling procedure or to the system (see Example 8.9 later in this section).

As you already know, the precompiled form of a procedure is stored in the database and used whenever the stored procedure is executed. If you want to generate the compiled form each time the procedure is executed, use the WITH RECOMPILE option.

The EXECUTE AS clause specifies the security context under which to execute the stored procedure after it is accessed. By specifying the context in which the procedure is executed, you can control which user account the Database Engine uses to validate permissions on objects referenced by the procedure.

By default, only the members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, can use the CREATE PROCEDURE statement. However, the members of these roles may assign this privilege to other users by using the GRANT CREATE PROCEDURE statement. Example 8.6 shows the creation of the simple stored procedure for the project table.

USE sample;
GO
CREATE PROCEDURE increase_budget (@percent INT=5)
	AS UPDATE project
		SET budget = budget + budget*@percent/100;Code language: PHP (php)

The stored procedure increase_budget increases the budgets of all projects for a certain percentage value that is defined using the parameter @percent. The procedure also defines the default value (5), which is used if there is no argument at the execution time of the procedure.

In contrast to “base” stored procedures that are placed in the current database, it is possible to create temporary stored procedures that are always placed in the temporary system database called tempdb. You might create a temporary stored procedure to avoid executing a particular group of statements repeatedly within a connection. You can create local or global temporary procedures by preceding the procedure name with a single pound sign (#proc_name) for local temporary procedures and a double pound sign (##proc_name, for example) for global temporary procedures. A local temporary stored procedure can be executed only by the user who created it, and only during the same connection. A global temporary procedure can be executed by all users, but only until the last connection executing it (usually the creator’s) ends.

The life cycle of a stored procedure has two phases: its creation and its execution. Each procedure is created once and executed many times. The EXECUTE statement executes an existing procedure. The execution of a stored procedure is allowed for each user who either is the owner of or has the EXECUTE privilege for the procedure. The EXECUTE statement has the following syntax:

[[EXEC[UTE]] [@return_status =] {proc_name
	| @proc_name_var}
	{[[@parameter1 =] value | [@parameter1=] @variable [OUTPUT]] | DEFAULT}..
	[WITH RECOMPILE]Code language: PHP (php)

All options in the EXECUTE statement, other than return_status, have the equivalent logical meaning as the options with the same names in the CREATE PROCEDURE statement. return_status is an optional integer variable that stores the return status of a procedure. The value of a parameter can be assigned using either a value (value) or a local variable (@variable). The order of parameter values is not relevant if they are named, but if they are not named, parameter values must be supplied in the order defined in the CREATE PROCEDURE statement.

The DEFAULT clause supplies the default value of the parameter as defined in the procedure. When the procedure expects a value for a parameter that does not have a defined default and either a parameter is missing or the DEFAULT keyword is specified, an error occurs.

Example 8.7 shows the use of the EXECUTE statement.

USE sample;
EXECUTE increase_budget 10;Code language: PHP (php)

The EXECUTE statement in Example 8.7 executes the stored procedure increase_budget (Example 8.6) and increases the budgets of all projects by 10 percent each. Example 8.8 shows the creation of a procedure that references the tables employee and works_on.

USE sample;
GO
CREATE PROCEDURE modify_empno (@old_no INTEGER, @new_no INTEGER)
	AS UPDATE employee
		SET emp_no = @new_no
		WHERE emp_no = @old_no
	UPDATE works_on
		SET emp_no = @new_no
		WHERE emp_no = @old_noCode language: PHP (php)

The procedure modify_empno in Example 8.8 demonstrates the use of stored procedures as part of the maintenance of the referential integrity (in this case, between the employee and works_on tables). Such a stored procedure can be used inside the definition of a trigger, which actually maintains the referential integrity (see Example 14.3).

Example 8.9 shows the use of the OUTPUT clause.

USE sample;
GO
CREATE PROCEDURE delete_emp @employee_no INT, @counter INT OUTPUT
	AS SELECT @counter = COUNT(*)
		FROM works_on
		WHERE emp_no = @employee_no
	DELETE FROM employee
		WHERE emp_no = @employee_no
	DELETE FROM works_on
		WHERE emp_no = @employee_noCode language: PHP (php)

This stored procedure can be executed using the following statements:

DECLARE @quantity INT
EXECUTE delete_emp @employee_no=28559, @counter=@quantity OUTPUTCode language: CSS (css)

The preceding example contains the creation of the delete_emp procedure as well as its execution. This procedure calculates the number of projects on which the employee (with the employee number @employee_no) works. The calculated value is then assigned to the @counter parameter. After the deletion of all rows with the assigned employee number from the employee and works_on tables, the calculated value will be assigned to the @quantity variable.