Stored Procedures in SQL Server

The estimated reading time for this post is 6 minutes

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 sys.sp_configure .
  • Extended System Stored Procedure: This one extends the functionality of SQL Server. Most extended system stored procedures have an xp_prefix such as sys.xp_dirtree.

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.

Statement Not Permitted

CREATE AGGREGATECREATE RULE
CREATE DEFAULTCREATE SCHEMA
CREATE or ALTER FUNCTIONCREATE or ALTER TRIGGER
CREATE or ALTER PROCEDURECREATE or ALTER VIEW
SET PARSEONLYSET SHOWPLAN_ALL
SET SHOWPLAN_TEXTSET SHOWPLAN_XML
USE databasename

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 QUOTED_IDENTIFIER and 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

The 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

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.

Saeed Jabarpourfard

Saeed is a Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer, with extensive experience in Database Development and Administration. He has been working with SQL Server since year 2010, and he used SQL Server 2008 R2 as his first RDBMS.

More Posts - Website


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz