A stored procedure in SQL Server is a precompiled collection of Transact-SQL statements, variables, and control-of-flow statements typically grouped together to perform a specific task. Stored procedures encapsulate code as a single module for processing. Statements in the procedure usually include DML statements, DDL statements, control-of-flow statements, comments, and calls to .NET Framework CLR methods. The code within a stored procedure is executed as a single unit or batch. The benefit of this is that the network traffic is greatly reduced as several Transact-SQL statements contained in the stored procedure are not required to travel through the network individually. Only the name of the stored procedure and its parameters are transmitted over the network.
The stored procedure runs faster than ad hoc Transact-SQL batches, especially when used in repetitive tasks. This is because SQL Server always caches a stored procedure execution plan in an area of SQL Server memory called procedure cache, and it is likely to remain in the procedure cache (provided there is enough memory available; unless run with RECOMPILE
option) and be reused, while ad hoc SQL plans created when running ad hoc Transact-SQL statements might or might not be stored in the procedure cache. Therefore, SQL Server does not retrieve and reload the stored procedure from disk and nor does it parse, optimize, and compile the stored procedure each time it runs.
Since database operations can be performed within stored procedures, they provide a high level of security. Instead of access granted to the underlying object, permission can be granted for the stored procedure.
The stored procedure allows modular programming, which allows you to break database procedures down into smaller, more manageable pieces.
You can create stored procedures that accept input parameters and return values and status information. Stored procedures use variables for internal temporary data storage, input parameters, and output parameters.
Microsoft SQL Server 2014 has four types of stored procedures, listed as follows:
CREATE PROCEDURE
statement.sp_
.tempdb
. We can create two types of temporary stored procedures: local and global. The name of a local temporary stored procedure must begin with a single number sign (#
), and the name of the global temporary stored procedure must begin with two number signs (##
). Local temporary stored procedures are only available to the user session that created the stored procedure. Therefore, SQL Server removes local temporary stored procedures when the user session ends. On the other hand, global temporary stored procedures are available for all user sessions after their creation. SQL Server removes global temporary stored procedures once all user sessions that refer to it are disconnected.
Extended user-defined stored procedures have been replaced by CLR stored procedures. A detailed discussion of CLR stored procedures is beyond the scope of this chapter. For more information about CLR stored procedures, see CLR Stored Procedure at http://msdn.microsoft.com/en-us/library/ms131094.aspx.
This chapter only focuses on user-defined stored procedures.
Now that we understand the purpose of stored procedures, let's take a look at the syntax of the CREATE PROCEDURE
statement. Have a look at the following code:
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } [;] <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ NATIVE_COMPILATION ] [SCHEMABINDING] [ EXECUTE AS Clause ]
The following are the arguments of the CREATE PROCEDURE
statement:
schema_name
: This specifies the name of the schema in which you are creating the stored procedure.procedure_name
: This specifies the name of the stored procedure; it must be unique within the schema.@parameter data_type
: This defines the stored procedure parameters.procedure_option
: These are used to further define the procedure. The following are the available options:ENCRYPTION
: This encrypts the text of the CREATE PROCEDURE
statement.SCHEMABINDING
: This binds the stored procedure to the underlying base objects, meaning users cannot modify the underlying base objects in any way that affects the stored procedure definition. This option is supported only for natively compiled stored procedures.NATIVE_COMPILATION
: This makes the stored procedure a natively compiled procedure.EXECUTE AS
: This specifies the context under which the stored procedure executes. We can set the execute context as CALLER
, SELF
, OWNER
, or as a username to identify a specific user.WITH RECOMPILE
: This recompiles stored procedures each time it runs.NOT FOR REPLICATION
: This specifies that the stored procedure cannot be executed on the subscribing server.AS
: This specifies the SQL statements (for Transact-SQL procedures) or module identifier (for CLR procedures) used to define a stored procedure.Here is an example of a stored procedure:
USE [AdventureWorks2012]; GO CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeInfo] @BusinessEntityID [int], @NationalIDNumber [nvarchar](15), @BirthDate [datetime], @MaritalStatus [nchar](1), @Gender [nchar](1) WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; BEGIN TRY UPDATE [HumanResources].[Employee] SET [NationalIDNumber] = @NationalIDNumber ,[BirthDate] = @BirthDate ,[MaritalStatus] = @MaritalStatus ,[Gender] = @Gender WHERE [BusinessEntityID] = @BusinessEntityID; END TRY BEGIN CATCH EXECUTE [dbo].[uspLogError]; END CATCH; END; GO
For a natively compiled stored procedure example, first create a memory-optimized table called Sales.SalesOrderDetail_MO
in the AdventureWorks2012
database. To create this table, run the following code snippet:
USE [master]; GO ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [AW_MEMORYOPTIMIZED] CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE [AdventureWorks2012] ADD FILE (NAME='AdventureWorks2012_MemoryOptimized', FILENAME='C:SQLDataAdventureWorks2012_MO.ndf') TO FILEGROUP [AW_MemoryOptimized]; GO USE [AdventureWorks2012]; GO CREATE TABLE Sales.SalesOrderDetail_MO ( [SalesOrderID] [int] NOT NULL ,[SalesOrderDetailID] [int] NOT NULL ,[CarrierTrackingNumber] [nvarchar](25) NULL ,[OrderQty] [smallint] NOT NULL ,[ProductID] [int] NOT NULL ,[SpecialOfferID] [int] NOT NULL ,[UnitPrice] [money] NOT NULL ,[UnitPriceDiscount] [money] NOT NULL ,[LineTotal] [money] ,[rowguid] [uniqueidentifier] NOT NULL ,[ModifiedDate] [datetime] ,CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID2] PRIMARY KEY NONCLUSTERED HASH ( [SalesOrderID] ,[SalesOrderDetailID]) WITH (BUCKET_COUNT = 20000)) WITH (MEMORY_OPTIMIZED = ON ,DURABILITY = SCHEMA_AND_DATA); GO
Next, enter and execute the following T-SQL code to copy the data from Sales.SalesOrderDetail
to our memory-optimized table, Sales.SalesOrderDetail_MO
:
USE [AdventureWorks2012]; GO INSERT INTO [Sales].[SalesOrderDetail_MO] SELECT [SalesOrderID] ,[SalesOrderDetailID] ,[CarrierTrackingNumber] ,[OrderQty] ,[ProductID] ,[SpecialOfferID] ,[UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] ,[ModifiedDate] FROM [Sales].[SalesOrderDetail]; GO
Now that we have created a memory-optimized table in the AdventureWorks2012
database and copied data into it, we are ready for a natively compiled stored procedure example. To create a natively compiled stored procedure, enter and execute the following T-SQL code to retrieve the orders' detail information from the Sales.SalesOrderDetail_MO
table:
USE [AdventureWorks2012]; GO CREATE PROCEDURE RetriveOrderDetail @SalesOrderID [int] WITH NATIVE_COMPILATION ,SCHEMABINDING ,EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT ,LANGUAGE = 'English') SELECT [SalesOrderID] , [SalesOrderDetailID] , [CarrierTrackingNumber] , [OrderQty] , [ProductID] , [SpecialOfferID] , [UnitPrice] , [UnitPriceDiscount] , [LineTotal] , [rowguid] , [ModifiedDate] FROM [Sales].[SalesOrderDetail_MO] WHERE [SalesOrderID] = @SalesOrderID; END; GO
The Transact-SQL code that actually makes the store procedure a natively compiled procedure is as follows:
WITH NATIVE_COMPILATION ,SCHEMABINDING ,EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT ,LANGUAGE = 'English')
We use a ALTER PROCEDURE
statement to modify the stored procedure definition. The following is the ALTER PROCEDURE
syntax, which is the same as the CREATE PROCEDURE
syntax:
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }[;] <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ NATIVE_COMPILATION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]
The following is an example of the ALTER PROCEDURE
statement:
USE [AdventureWorks2012]; GO ALTER PROCEDURE [HumanResources].[uspUpdateEmployeeInfo] @BusinessEntityID [int], @NationalIDNumber [nvarchar](15), @BirthDate [datetime], @MaritalStatus [nchar](1), @Gender [nchar](1) WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; BEGIN TRY UPDATE [HumanResources].[Employee] SET [NationalIDNumber] = @NationalIDNumber ,[BirthDate] = @BirthDate ,[MaritalStatus] = @MaritalStatus ,[Gender] = @Gender WHERE [BusinessEntityID] = @BusinessEntityID; END TRY BEGIN CATCH EXECUTE [dbo].[uspLogError]; END CATCH; END; GO
We use the DROP PROCEDURE
statement to permanently delete a stored procedure. The DROP PROCEDURE
statement syntax is as follows:
DROP PROC[EDURE] [schema.]procedure_name
The following is an example of the DROP PROCEDURE
statement:
USE [AdventureWorks2012]; GO DROP PROCEDURE [HumanResources].[uspUpdateEmployeeInfo]; GO
The following are the steps to drop a stored procedure in SSMS 2014:
Databases
folder.Programmability
.Stored Procedures
.Stored Procedures
, right-click on the procedure and choose Delete from the context menu.The following are the steps to view stored procedures in SSMS 2014:
Databases
folder.Programmability
.Stored Procedures
. We can now view the stored procedures, as shown in the following screenshot:You can also either use the sp_helptext
system stored procedure or query the sys.sql_modules
or sys.syscomments
system view to view the definition of a stored procedure and the statement that was used to create the procedure. (The sp_helptext
system stored procedure and the sys.syscomments
system view can also be used to print the definition of a trigger, a view, a rule, or a default.)
Although you can run a stored procedure by simply typing in the name of the stored procedure in SSMS 2014 Query Editor, this is not the recommended way to run the stored procedure. The recommended way is to use the EXECUTE
keyword followed by a stored procedure. The syntax for this is as follows:
EXECUTE | EXEC procedure_name [parameter1, parameter2, n…]
For example, enter and execute the following T-SQL code in SSMS 2014 Query Editor to execute the dbo.uspGetWhereUsedProductID
stored procedure:
USE [AdventureWorks2012] GO DECLARE @RC INT DECLARE @StartProductID INT = 20 DECLARE @CheckDate DATETIME = CURRENT_TIMESTAMP - 40 EXECUTE @RC = [dbo].[uspGetWhereUsedProductID] @StartProductID, @CheckDate; GO
3.14.251.57