Stored Procedures can be defined as compiled SQL statements stored in the database.
Using stored procedures has many advantages over simply calling a query from the front-end, especially in web applications and large size applications.
Advantages of Stored Procedures:
1. Since the queries are executed as a batch, the network traffic is significantly reduced.
2. Execution time is greatly reduced due to the fact that the stored procedures are pre-compiled.
3. Once a stored procedure is executed, it will reside in the cache of the SQL Server. As a result, any of the subsequent calls will be speeded up.
4. Stored procedures provide a different layer of abstraction for the application and has two main advantages.
1. First, without changing the application, we can easily modify the stored procedure when business rules change.
2. More than that, they provide more efficient and secure data access.
Creating a Stored Procedure
Syntax:
CREATE PROC [ EDURE ] procedure_name [ ;number ]
[ { @parameter data_type }
[ VARYING ] [ =default ] [ OUTPUT ]
] [ ,… ]
[ WITH
{
RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
[ FOR REPLICATION ]
AS
sql_statement
procedure_name
Is the name of the stored procedure to be created.
number
Is an optional parameter, which is used to group procedures of the same name so they can be dropped using a single DROP PROCEDURE statement.
@parameter
Is a parameter in the procedure. There can be one or more parameters.
data_type
Is the data type of the given parameter.
VARYING
Specifies the result-set supported as an output. Applies only to the cursor parameters.
default
Is a default value for the parameter.
OUTPUT
Indicates that the given parameter is a return parameter.
RECOMPILE
RECOMPILE indicates that SQL Server does not keep the cache for the procedure and it is recompiled each time when it is executed.
ENCRYPTION
ENCRYPTION indicates that the SQL will prevent the procedure from being published as part of SQL Server replication.
FOR REPLICATION
This is used to specify that stored procedures created for replication cannot be executed on the subscribing server and is executed only during replication.
AS
The keyword used just before the SQL statements in the procedure.
sql_statement
Is the SQL statement that is to be executed in the procedure.
The CREATE PROCEDURE (or CREATE PROC) statement is used to create a stored procedure.
Examples
Code:
CREATE PROCEDURE spDisplayAll
AS
SELECT * FROM Students
GO
Output:
The command(s) completed successfully.
Explanation:
In the above example, we have created a stored procedure spDisplayAll that fetches all of the records in the Students table.
Executing a Stored Procedure
Syntax:
[ EXEC [ UTE ] ]
{ procedure_name [ ;number ] | @procedure_name_variable }
[ [ @parameter= ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ,… } ]
[ WITH RECOMPILE ]
procedure_name
Is the name of the stored procedure.
number
An optional integer used to group procedures of the same name so they can be dropped using a single DROP PROCEDURE statement.
@procedure_name_variable
The locally defined variable that represents a stored procedure name.
@parameter
Is the parameter for a procedure, as defined in the CREATE PROCEDURE statement.
value
Is the value of the parameter to the procedure. If parameter names are not specified, parameter values must be supplied in the order defined in the CREATE PROCEDURE statement.
@variable
Is the variable that stores a parameter or a return parameter.
OUTPUT
Specifies that the stored procedure returns a parameter.
DEFAULT
Supplies the default value of the parameter as defined in the procedure.
Stored procedures can be run by using the EXEC or EXECUTE command. Parameter values can be supplied if a stored procedure is written to accept them.
Examples
Code:
EXEC spDisplayAll;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
1 Job Mathew 2 12345 2
2 Rock Feller 4 46565 3
3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2
5 Nadia Alex 0 78565 4
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(5 row(s) affected)
Explanation:
When we execute the spDisplayAll stored procedure, using the EXEC command, we get all the records in the ‘Students’ table.
Using Parameters
Parameters can be passed to the stored procedures. This makes the procedure dynamic.
The following points are to be noted:
o One or more number of parameters can be passed in a procedure.
o The parameter name should proceed with an @ symbol.
o The parameter names will be local to the procedure in which they are defined.
The parameters are used to pass information into a procedure from the line that executes the parameter. The parameters are given just after the name of the procedure on a command line. Commas should separate the list of parameters.
The values can be passed to stored procedures by:
1. By supplying the parameter values exactly in the same order as given in the CREATE PROCEDURE statement.
2. By explicitly naming the parameters and assigning the appropriate value.
Examples
Code:
CREATE PROCEDURE spSelectStudent (@Course INTEGER, @Grade INTEGER)
AS
SELECT * FROM Students
WHERE Std_Course=@Course AND Std_Grade <= @Grade
GO
EXEC spSelectStudent 3, 2;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(2 row(s) affected)
Explanation:
In the above example, the procedure is defined with two parameters. It should be noted that while executing the procedure the parameters should be passed in the same order of that in the CREATE statement. In this case, the first argument corresponds to Std_Course and second argument to Std_Grade.
Language(s): MS SQL Server
Code:
EXEC spSelectStudent @Course=3, @Grade=2;
EXEC spSelectStudent @Grade=2, @Course=3;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(2 row(s) affected)
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(2 row(s) affected)
Explanation:
Here since we are explicitly naming the parameters and assigning the appropriate value, the stored procedure allows the parameters to be supplied in any order.
Language(s): MS SQL Server
Code:
CREATE PROCEDURE spSelectStudent1 (@Course INTEGER=2, @Grade INTEGER=3)
AS
SELECT * FROM Students
WHERE Std_Course=@Course AND Std_Grade <= @Grade
GO
EXEC spSelectStudent1;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
1 Joe Mathew 2 12345 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
The stored procedures can be created with optional parameters with default values, so that if no values are assigned to the parameters then the default value will be taken as the value of the parameter.
In the above example, the procedure is executed without any parameter. So it takes the default parameters, which are @Course as 2, @Grade as 3.
Language(s): MS SQL Server
Code:
EXEC spSelectStudent1 @Course=4, @Grade=4;
Output:
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
2 Rock Feller 4 46565 3
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
In the above example, the procedure takes the specified parameters of @Course as 4, @Grade as 4.
Deleting a Stored Procedure
Syntax:
DROP PROCEDURE procedure_name, …
procedure_name
Is the name of the stored procedure or stored procedure group to be removed.
When a stored procedure is no longer needed, it can be deleted using the DROP PROCEDURE command.
Examples
Code:
DROP PROCEDURE spSelectStudent1;
Output:
The command(s) completed successfully.
Explanation:
The above DROP command deletes the stored procedure spSelectStudent1 from the database.
Modifying a Stored Procedure
Syntax:
ALTER PROC [ EDURE ] procedure_name [ ;number ]
[ { @parameter data_type }
[ VARYING ] [ =default ] [ OUTPUT ]
] [ ,… ]
[ WITH
{
RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
[ FOR REPLICATION ]
AS
sql_statement
The stored procedure modification is usually done using the ALTER PROCEDURE statement. It can also be done by deleting the existing stored procedure and then creating a new one with the same name. If we are using the ALTER PROCEDURE statement any of the permissions associated with the stored procedure are retained. In the other case, however, the permissions will be lost.
We can alter a stored procedure so that only the parameter definition is changed and not the permissions that are set for the stored procedure. The parameters from the ALTER PROCEDURE statement are the same as the ones for the CREATE PROCEDURE statement.
Examples
Code:
CREATE PROCEDURE spGetAvgGrade
AS
SELECT AVG(Std_Grade) FROM Students
GO
EXEC spGetAvgGrade;
Output:
AverageGrade
________________________________________
2
________________________________________
(1 row(s) affected)
Language(s): MS SQL Server
Code:
ALTER PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
GO
EXEC spGetAvgGrade 3;
Output:
AverageGrade
________________________________________
1
________________________________________
(1 row(s) affected)
Explanation:
This example demonstrates how to use the ALTER PROCEDURE command to modify a procedure and then execute it.
Language(s): MS SQL Server
Code:
DROP PROCEDURE spGetAvgGrade;
CREATE PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
GO
EXEC spGetAvgGrade 3;
Output:
The command(s) completed successfully.
AverageGrade
________________________________________
1
________________________________________
(1 row(s) affected)
Explanation:
In the above example, we first delete the existing procedure and then recreate the procedure.
Renaming a Stored Procedure
Syntax:
sp_rename ‘procedure_name1’, ‘procedure_name2’
procedure_name1
The current name of the stored procedure
procedure_name2
The new name of the stored procedure.
A stored procedure can be renamed. The new name should follow the rules for identifiers.
Examples
Code:
EXEC sp_rename ‘spGetAvgGrade’, ‘spNewAvgGrade’;
Output:
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘spNewAvgGrade’.
Explanation:
In the above example we change the name of the stored procedure spGetAvgGrade to spNewAvgGrade.
To View a Stored Procedure
Syntax:
1. To view the definition of a stored procedure:
sp_helptext procedure_name
2. To view the information about a stored procedure:
sp_help procedure_name
3. To view the dependencies of the stored procedure:
sp_depends procedure_name
procedure_name
Is the name of the stored procedure.
SQL Server allows us to view the definition, information, and dependencies of a stored procedure.
Examples
Code:
sp_helptext spNewAvgGrade;
Output:
CREATE PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
Explanation:
In the above example, the sp_helptext displays the text of the spNewAvgGrade stored procedure.
Language(s): MS SQL Server
Code:
sp_help spNewAvgGrade;
Output:
Name Owner Type Created_datetime
________________________________________ ________________________________________ ________________________________________ ________________________________________
spNewAvgGrade dbo stored procedure 2003-09-14 23:53:13.810
________________________________________ ________________________________________ ________________________________________ ________________________________________
Parameter_name Type Length Prec Scale Param_order
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
@Course int 4 10 0 1
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
Explanation:
This example displays information about the stored procedure.
Language(s): MS SQL Server
Code:
sp_depends spNewAvgGrade;
Output:
In the current database, the specified object references the following:
Name Type Updated Selected Column
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
dbo.Students user table no no Std_Course
dbo.Students user table no no Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
Explanation:
This example shows the dependencies of the stored procedure.