Execute Batches multiple times using Stored Procedures in SQL

Batches are temporary in nature. To execute a batch more than once, you need to recreate SQL statements and submit them to the server. This leads to an increase in the overhead, as the server needs to compile and create the execution plan for these statements again. Therefore, if you need to execute a batch multiple times, you can save it within a stored procedure. A stored procedure is a precompiled object stored in the database.

Stored procedures can invoke the Data Definition Language (DDL) and Data Manipulation Language (DML) statements and can return values. If you need to assign values to the variables declared in the procedures at the run time, you can pass parameters while executing them. You can also execute a procedure from another procedure. This helps in using the functionality of the called procedure within the calling procedure.

Creating Stored Procedures

You can create a stored procedure by using the CREATE PROCEDURE statement. The syntax of the CREATE PROCEDURE statement is:

CREATE PROCEDURE proc_name
AS
BEGIN
Sql_statement1
Sql_statement2
END

Where Proc_name specifies the name of the stored procedure.

The following example create a stored procedure to view the department names from the Department table:

CREATE PROCEDURE prcDept
AS
BEGIN
SELECT Name FROM HumanResources.Department
END

When the CREATE PROCEDURE statement is executed, the server compiles the procedure and saves it as a database object. The procedure is then available for various applications to execute. The process of compiling a stored procedure involves the following steps:

• The procedure is compiled and its components are broken into various pieces. This process is known as parsing.
• The existence of the referred objects, such as tables and views, are checked. This process is known as resolving.
• The name of the procedure is stored in the sysobjects table and the code that creates the stored procedure is stored in the syscomments table.
• The procedure is compiled and a blueprint for how the query will run is created. This blueprint is specified as execution plan. The execution plan is saved in the procedure cache.
• When the procedure is executed for the first time. The execution plan will be read and fully optimized and then run. The net time the procedure is executed in the same session, it will be read directly from the cache. This increases performance, as there is no repeated compilation.

After creating the stored procedure, you can view the code of the procedure by using the sp_helptext command.

Calling a Procedure from another Procedure: SQL

At times, you might need to use the values returned by a procedure in another procedure. For this, you can execute or call one procedure from within another procedure.

A procedure that calls or executes another procedure is known as the calling procedure, and the procedure that is called or executed by the calling procedure is termed as the called procedure. You can also execute a procedure from another procedure if you need to use the functionality provide by one into another.

Consider the previous example where the prcGetEmployeeDetail procedure returns the employee details for a given employee ID. You can create the prcDisplayEmployeeStatus procedure, which accepts the employee ID of an employee as input and displays the department name and shift ID where the employee is working along with the manager ID and the title employee. To perform this task, you need to call the prcGetEmployeeDetail procedure from the prcDisplayEmployeeStatus procedure, as shown in the following statement:

CREATE PROCEDURE prcDisplayEmployeeStatus @EmpId int
AS
BEGIN
DECLARE @DepName char (50)
DECLARE @ShiftId int
DECLARE @ReturnValue int
EXEC @ReturnValue = prcGetEmployeeDetail @EmpId,
@ DepName OUTPUT,
@ ShiftId OUTPUT
IF (@ReturnValue = 0)
BEGIN
PRINT ‘The details of an employee with ID: ‘ + convert (char (10), @EmpId)
PRINT ‘shift ID: ‘ + convert ( char (1), @shiftId)
SELECT ManagerID, Title FROM HumanResources.Employee
WHERE EmployeeID = @EmpID
END
ELSE
PRINT ‘No records found for the given employee’
END

To execute the preceding code, you need to execute the following statement:
EXEC prcDisplayEmployeeStatus 2

SQL Server provides a function, @@ROWCOUNT, which return the number of rows affected by the last statement. You can use this statement in the IF construct to check the result of the last statement that executed.

How to Return values from Stored Procedure: SQL

Similar to providing input values to the procedures at run time, you can also return values as output from the procedures. The values can be returned to the calling application through output parameters. To specify a parameter as the output parameter, you can use the OUTPUT keyword.

The OUTPUT keyword has to be specified in both the CREATE PROCEDURE and the EXECUTE statement. If the OUTPUT keyword is omitted, the procedure will be executed but will not return any value.

The syntax of the declaring an output parameter using the OUTPUT keyword is:

CREATE PROCEDURE procedure_name
[
{ @parameter data_type} [OUTPUT]
]
AS
Sql_statement […n]

• @parameter data_type [OUTPUT] allows the stored procedure to pass a data value to the calling procedure. If the OUTPUT keyword is not used, then the parameter is treated as an input parameter.

You can also return values from the stored procedure by using the RETURN statement. The RETURN statement allows the stored procedure to return only an integer value to the calling application. The syntax of the RETURN statement is:

RETURN value
Where,

• Value is any integer. If a value is not specified, then the stored procedure returns a default value of 0 to specify failure and 1 to specify success.

Consider an example. You need to display the details of an employee whose employee ID has been provided as an input. For this, you need to create a procedure prcGetEmployeeDetail thet will accept employee ID as input and will return the department name and ID of the shift in which the employee works. You can create the procedure, as shown in the following statement:

CREATE PROCEDURE prcGetEmployeeDetail @EmpID int, @DepName char (50) OUTPUT, @ShiftID int OUTPUT
AS
BEGIN
IF EXISTS (SELECT * FROM HumanResources.Employee WHERE EmployeeID = @EmpID)
BEGIN
SELECT @DepName = d. Name, @Shiftid = h.ShiftID
FROM HumanResourcess.Department d JOIN
HumanResources.EmployeeDepartmentHistory h
ON d.DepartID = h.DepartmentID
WHERE EmployeeID = @EmpId AND h.Enddate IS NULL RETURN 0
END
ELSE
RETURN 1
END

The preceding procedure accepts the employee ID as an input parameter and returns the department name and shift ID as output parameters. The procedure first checks the existence of the given employee ID. Id it exists, the procedure returns and integer value 0 along with the required details.

How to Create Parameterized Stored Procedure: SQL

At times, you need to execute a procedure for different values of a variable that are provided at run time. For this, you can create a parameterized stored procedure. Parameters are used to pass values to the stored procedure during run time. These values can be passed by using standard variables.

The parameter that passes the values is defined as input parameter. A stored procedure has the capability of using a maximum of 2100 parameters. Each parameter has a name, data type, direction, and a default value.

The following example creates a stored procedure displaying the employee ID, the login ID, and title of the employees that have the same title provided as an input during

Execution:

CREATE PROC prcListEmployee @title char (50)
AS
BEGIN
PRINT ‘List of Employees’
SELECT EmployeeID, LoginID, Title
FROM HumanResources.Employee
WHERE Title = @title
END

Execute the stored procedure, prcListEmployee, by using the following statement:

EXECUTE prcListEmployee ‘Tool Designer’

While executing the stored procedures, you can also provide the values for the parameters by explicity specifying the name and value of the parameter. In the previous example, you can also pass the parameter value by using the name of variable, as shown in the following SQL statement:

EXECUTE prcListEmployee @title = ‘Tool Designer’

Guidelines to Create and Execute Stored Procedure: SQL

To create stored procedure database developer have to keep some points in mind. The following points need to be considered before creating a stored procedure:

• You cannot combine the CREATE PROCEDURE statement with other SQL statements in a single batch.
• You must have the CREATE PROCEDURE permission to create a procedure in the database and the ALTER permission on the schema, where the procedure is being created.
• You can create a stored procedure only in the current database.
• After creating a stored procedure, you can execute the procedure. You can also alter the procedure definition or drop it, if not required.

Executing a Stored Procedure

A procedure can be executed by using the EXEC PROCEDURE statement. The syntax of the EXEC PROCEDURE statement is:

EXEC | EXECUTE PROCEDURE proc_name AS [ { LOGIN|USER} = ‘name’]
Where,

• Proc_name, is the name of the procedure you need to execute.
• LOGIN|USER specifies the name of the login of another user on the same database server or another user in the same database that you need to impersonate. While executing a procedure you can impersonate the rights of another user or ligin to execute the procedure. This allows a user who does not have permission to execute a procedure to execute it using the permissions assigned to other users.

Consider an example. Robert has created a stored procedure named DispEmpDetail that displays the details of the employees. Kim needs to execute the procedure but does not have the execution rights. In such a case, Kim can use the rights of Robert to execute. For this, Kim needs to impersonate Robert.

You can execute the stored procedure, prcDept, as shown in the following statement:
EXEC PROCEDURE prcDept

Altering Stored Procedure

A stored procedure can be modified by using the ALTER PROCEDURE statement. The syntax of the ALTER PROCEDURE statement is:

ALTER PROCEDURE proc_name

You can alter the stored procedure, prcDept, as followis:

ALTER PROCEDURE prcDept
AS
BEGIN
SELECT DepartmentID, Name FROM HumanResources.Department
END
In the preceding code, the DepartmentID attribute will be displayed along with the department name.

Dropping a Stored Procedure

You can drop a stored procedure from the database by using the DROP PROCEDURE statement. The syntax of the DROP PROCEDURE statement is:

DROP PROCEDURE proc_name

You cannot retrieve a procedure once it is dropped. You can drop the prcDept stored procedure by using the following statement:

DROP PROCEDURE prcDept