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.