Creating and using stored procedures

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.

Tip

You can run DBCC FREEPROCCACHE to manually clear the procedure cache.

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.

Note

You can create stored procedures in any SQL Server database except the resource database.

Microsoft SQL Server 2014 has four types of stored procedures, listed as follows:

  • User-defined stored procedures: These are procedures you write using the CREATE PROCEDURE statement.
  • Natively compiled stored procedures: These are user-defined stored procedures that operate on memory-optimized tables. Though natively compiled stored procedures are written in Transact-SQL, they are actually compiled to highly efficient machine code. This maximizes the runtime performance of certain workloads and types of queries because the generated machine code only contains exactly what is needed to run the request, and nothing more.
  • System stored procedures: These are the procedures shipped with SQL Server as part of the default installation. The system stored procedures are used to perform administrative procedures. System stored procedures have names that are prefixed with sp_.
  • Temporary stored procedures: These are like normal stored procedures, but they do not exist permanently. Instead, temporary stored procedures have a life and limited accessibility, depending on their type. Temporary stored procedures reside inside 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: These are the routines you write in a programming language, which are compiled as a dynamic link library (DLL) file. We write them using the SQL Server Extended Stored Procedure API.
  • CLR stored procedures: These are the procedures you write using the .NET framework programming language.

    Note

    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.

Creating a stored procedure

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.

    Note

    You can also recompile the stored procedure either by executing a stored procedure with the WITH RECOMPILE option or by running a sp_recompile stored procedure.

  • 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

Tip

Use SET NOCOUNT ON within stored procedures to increase performance. This is because, when specified, this statement does not return the number of rows affected.

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')

Modifying a stored procedure

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

Note

Note here that the operation ALTER PROCEDURE is not supported with natively compiled stored procedures.

Dropping a stored procedure

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:

  1. In Object Explorer, expand the Databases folder.
  2. Expand the database where the stored procedure you want to delete exists.
  3. Expand Programmability.
  4. Expand Stored Procedures.
  5. In Stored Procedures, right-click on the procedure and choose Delete from the context menu.
  6. SQL Server prompts you to verify your action. Click on OK to confirm.

Viewing stored procedures

The following are the steps to view stored procedures in SSMS 2014:

  1. In Object Explorer, expand the Databases folder.
  2. Expand the database whose stored procedures you want to view.
  3. Expand Programmability.
  4. Expand Stored Procedures. We can now view the stored procedures, as shown in the following screenshot:
    Viewing stored procedures

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.)

Note

You cannot use the sp_helptext system stored procedure and nor can you query the sys.sql_modules and sys.syscomments system views to view the stored procedure definition if a stored procedure was created using the WITH ENCRYPTION option.

Executing stored procedures

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
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.14.251.57