A stored procedure is a named collection of Transact-SQL statements that is stored on the server within the database itself. A stored procedure could be created at the server level to encapsulate all of the Transact-SQL statements that are required. Stored procedures are given names and are called by name. The application can then simply ask to execute the stored procedure each time it needs to use that same functionality, rather than sending all of the statements that would otherwise be required. Stored procedures are similar to procedures, methods, and functions in high-level languages. They can have input and output parameters and a return value. Stored procedures can be created in either Transact-SQL code or managed .NET code and are run by the EXECUTE Transact-SQL statement.
Stored Procedure can brings the following benefits:-
- Security Boundary: Users can be given permission to execute a stored procedure without being given permission to access the objects that the stored procedure accesses.
- Modular Programming: Stored procedures help by enabling logic to be created once and then enabling the logic to be called many times and from many applications.
- Delayed Binding: It is possible to create a stored procedure that accesses (or references) a database object that does not yet exist. This can be helpful in simplifying the order in which database objects need to be created. This is referred to as deferred name resolution.
- Performance: A single statement requested across the network can execute hundreds of lines of Transact-SQL code. It has better opportunities for execution plan reuse.
There are also two basic types of stored procedure:-
- System Stored Procedure: This one typically used for administrative purposes either to configure servers, databases, or objects or to view information about them. Most system stored procedures have an sp_prefix such as
- Extended System Stored Procedure: This one extends the functionality of SQL Server. Most extended system stored procedures have an xp_prefix such as
Key difference is how they are coded, system stored procedures are Transact-SQL code in the master database while extended system stored procedures are references to DDLs.
For the statements that are not permitted, the reason usually relates to one of the following:
Creation of other objects.
Changing SET options that relate to query plans.
Changing database context by using the USE statement.
Note that stored procedures can access objects in other databases, but the objects must be referred to by name, not by attempting to change the database context to another database. That is, you cannot use the USE statement within the body of a stored procedure in the way that you can use it in a Transact-SQL script.
Create Stored Procedure
Creating a stored procedure requires both the
CREATE PROCEDURE permission in the current database and the
ALTER permission on the schema in which the procedure is being created. It is important to keep connection settings such as
ANSI_NULLS consistent when you are working with stored procedures. The settings that are associated with the stored procedure are taken from the settings in the session where it is created.
When you are working with stored procedures, a good practice is first to write and test the Transact-SQL statements that you want to include in your stored procedure and then, if you receive the results that you expected, wrap the Transact-SQL statements in a
CREATE PROCEDURE statement.
CREATE PROC sales.Getsalespersonsnames AS BEGIN SELECT sp.BusinessEntityID, p.Lastname, p.Firstname FROM sales.salesperson AS sp INNER JOIN person.person AS p ON sp.BusinessEntityID=p.BusinessEntityID WHERE sp.TerritoryID IS NOT NULL ORDER BY sp.BusinessEntityID END
Executing Stored Procedure
EXECUTE statement is mostly used to execute stored procedures, but can also be used to execute other objects such as dynamic Structured Query Language (SQL) statements.
Use two-part naming when you are executing local stored procedures within a database. Otherwise, SQL Server searches for the stored procedure in the schema of the current database, then in the caller’s default schema of the current database and at last in the dbo schema in the current database.
EXECUTE Sales.Getsalespersonsnames; --OR EXEC Sales.Getsalespersonsnames;
Altering a Stored Procedure
You use the Transact-SQL
ALTER PROCEDURE/ALTER PROC statement to replace an existing procedure. The main reason for using the
ALTER PROCEDURE statement is to retain any existing permissions on the procedure while it is being changed. Note that when you alter a stored procedure, you need to resupply any options (such as the
WITH ENCRYPTION clause) that were supplied while creating the procedure.
ALTER PROC sales.Getsalespersonsnames AS BEGIN SELECT sp.BusinessEntityID, p.Lastname, p.Firstname FROM sales.salesperson AS sp INNER JOIN person.person AS p ON sp.BusinessEntityID=p.BusinessEntityID WHERE sp.TerritoryID IS NOT NULL AND sp.Bonus>=2000 ORDER BY sp.BusinessEntityID END
[info]Creating procedures are required to follow the below characteristics to make sure the performance of stored procedure remains intact.
- Qualify names inside stored procedures
- Keep consistent SET options
- Apply consistent naming conventions (and no sp_prefix)
- Use @@nestlevel to see correct nesting level (32 is the maximum number of levels)
- Keep to one procedure for each task
Parametrized Stored Procedures
Stored procedures are more flexible when you include parameters as part of the procedure definition because you can create more generic application logic. Stored procedures can use both input and output parameters and return values. Provide default values for a parameter where appropriate. If a default is defined, a user can execute the stored procedure without specifying a value for that parameter. In the below example default value for
@Year is 2005.
CREATE PROC Sales.Getsalespersonsubtotal @SalesPersonID smallint , @Year datetime=2005 AS BEGIN SELECT S.SalesPersonID , P.FirstName , P.LastName , YEAR(S.OrderDate) AS [Year], SUM(S.SubTotal) as SubTotal FROM Sales.SalesOrderHeader AS S INNER JOIN Person.Person AS P ON S.SalesPersonID=P.BusinessEntityID GROUP BY S.SalesPersonID , P.FirstName , P.LastName ,YEAR(S.OrderDate) HAVING S.SalesPersonID=@SalespersonID AND YEAR(S.OrderDate)=@Year END
For executing a stored procedure by using input parameters, you can proceed one of these ways.
EXECUTE Sales.Getsalespersonsubtotal 275 , 2006 EXECUTE Sales.Getsalespersonsubtotal @SalesPersonID=275 , @Year=2006 EXECUTE Sales.Getsalespersonsubtotal 275 EXECUTE Sales.Getsalespersonsubtotal @Year=2006 , @SalesPersonID=275
CREATE PROC Sales.OrderCount @OrderDate date , @OrderCount int output AS BEGIN SELECT S.OrderDate , COUNT(S.OrderDate) AS TotalCount FROM Sales.SalesOrderHeader AS S GROUP BY S.OrderDate HAVING S.OrderDate=@OrderDate SELECT @OrderCount=COUNT(S.OrderDate) FROM Sales.SalesOrderHeader AS S GROUP BY S.OrderDate HAVING S.OrderDate=@OrderDate END
DECLARE @TotalOrder int EXECUTE Sales.OrderCount '7-1-2005', @TotalOrder output SELECT @TotalOrder AS OrderCount
In addition to
ENCRYPTION , you can perform
RECOMPILE, EXECUTE AS before stored procedure’s body by
WITH clause and
OPTION(OPTIMIZE FOR) in stored procedure’s body. You can add a
WITH RECOMPILE option when you are declaring a stored procedure. This causes the procedure to be recompiled every time it is executed. If you add
WITH RECOMPILE to the
EXEC statement, SQL Server will recompile the procedure before running it and will not store the resulting plan. In this case, the original plan would be preserved and can be reused later. There is an
OPTION (OPTIMIZE FOR) query hint that enables you to specify the value of a parameter that should be assumed when compiling the procedure, regardless of the actual value of the parameter. The
EXECUTE AS clause sets the execution context of modules such as stored procedures. It is useful when you need to override the default security context.
Stored procedure is compiled object in SQL Server therefore the performance compare to ad-hoc object/query is much higher and uses minimum resources. It is recommended and best practices to use stored procedures; I hope this blog post was informative for you, please share it with your professional network if it is so.