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 Chapter 9.
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 that 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.
NOTE Stored procedures can be natively compiled, meaning that the particular procedure is compiled when it is created, rather than when it is executed. This special form of stored procedures is described in Chapter 21 (see Example 21.7).
1. 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
[({@paraml} typel [ VARYING] [= defaultl] [OUTPUT])] {, …}
[WITH {RECOMPILE | ENCRYPTION | EXECUTE AS ‘user_name’}]
[FOR REPLICATION]
AS batch | EXTERNAL NAME method_name
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 (demonstrated a bit later in Example 8.9).
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.
NOTE The use of the WITH RECOMPILE option destroys one of the most important benefits of the stored procedures: the performance advantage gained by a single precompilation. For this reason, the WITH RECOMPILE option should be used only when database objects used by the stored procedure are modified frequently or when the parameters used by the stored procedure are volatile.
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. (For the discussion of user permissions, fixed server roles, and fixed database roles, see Chapter 12.)
Example 8.6 shows the creation of the simple stored procedure for the project table.
Example 8.6
USE sample;
GO
CREATE PROCEDURE increase_budget (@percent INT=5)
AS UPDATE project
SET budget = budget + budget*@percent/100;
NOTE The GO statement is used to separate two batches. (The CREATE PROCEDURE statement must be the first statement in the batch.)
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.
NOTE It is possible to create stored procedures that reference nonexistent tables. This feature allows you to debug procedure code without creating the underlying tables first, or even connecting to the target server.
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. Analogous to local and global temporary tables, 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 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 (see Chapter 12). The EXECUTE statement has the following syntax:
[[EXEC[UTE]] [@return_status =] {proc_name
| @proc_name_var}
{[[@parameter1 =] value | [@parameter1=] @variable [OUTPUT]] | DEFAULT}..
[WITH RECOMPILE]
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.
NOTE When the EXECUTE statement is the first statement in a batch, the word “EXECUTE” can be omitted from the statement. Despite this, it would be safer to include this word in every batch you write.
Example 8.7 shows the use of the EXECUTE statement.
Example 8.7
USE sample;
EXECUTE increase_budget 10;
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.
Example 8.8
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_no
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). To execute this procedure, use the EXECUTE statement and assign values to both input parameters, @old_no and @new_no. Example 8.9 shows the use of the OUTPUT clause.
Example 8.9
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_no;
This stored procedure can be executed using the following batch:
DECLARE @quantity INT;
EXECUTE delete_emp @employee_no=28559, @counter=@quantity OUTPUT;
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.
NOTE The value of the parameter will be returned to the calling procedure if the OUTPUT option is used. In Example 8.9, the delete_emp procedure passes the @counter parameter to the calling statement, so the procedure returns the value to the system. Therefore, the @counter parameter must be declared with the OUTPUT option in the procedure as well as in the EXECUTE statement.
2. The EXECUTE Statement with RESULT SETS Clause
Using the WITH RESULT SETS clause for the EXECUTE statement, you can change conditionally the form of the result set of a stored procedure.
The following two examples help to explain this clause. Example 8.10 is an introductory example that shows how the output looks when the WITH RESULT SETS clause is omitted.
Example 8.10
USE sample;
GO
CREATE PROCEDURE employees_in_dept (@dept CHAR(4))
AS SELECT emp_no, emp_lname
FROM employee
WHERE dept_no IN (SELECT @dept FROM department
GROUP BY dept_no)
employees_in_dept is a simple stored procedure that displays the numbers and family names of all employees working for a particular department. (The department number is a parameter of the procedure and must be specified when the procedure is invoked.) The result of this procedure is a table with two columns, named according to the names of the corresponding columns (emp_no and emp_lname). To change these names (and their data types, too), use the WITH RESULTS SETS clause (see Example 8.11).
Example 8.11
USE sample;
EXEC employees_in_dept ‘d1’
WITH RESULT SETS
( ([EMPLOYEE NUMBER] INT NOT NULL,
[NAME OF EMPLOYEE] CHAR(20) NOT NULL));
The output is
As you can see, the WITH RESULT SETS clause in Example 8.11 allows you to change the name and data types of columns displayed in the result set. Therefore, this functionality gives you the flexibility to execute stored procedures and display the output result sets in another form.
NOTE The Database Engine provides capabilities to create temporary stored procedures, too.
The semantics of temporary stored procedures is the same as the semantics of the temporary tables. In other words, a scope and lifetime of temporary stored procedures is for the duration of a session, and you use the symbol # as a prefix to specify temporary stored procedures.
3. Changing the Structure of Stored Procedures
The Database Engine also supports the ALTER PROCEDURE statement, which modifies the structure of a stored procedure. The ALTER PROCEDURE statement is usually used to modify Transact-SQL statements inside a procedure. All options of the ALTER PROCEDURE statement correspond to the options with the same name in the CREATE PROCEDURE statement. The main purpose of this statement is to avoid reassignment of existing privileges for the stored procedure.
A stored procedure is removed using the DROP PROCEDURE statement. Only the owner of the stored procedure and the members of the db_owner and sysadmin fixed roles can remove the procedure.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.