Stored Procedures

sp_help, sp_helptext, sp_who2, sp_lock, sp_rename, sp_configure, sp_spaceused, sp_executesql, sp_depends (deprecated; use sys.dm_sql_referencing_entities), sp_describe_first_result_set, sp_recompile, sp_procoption (startup procs).

19.1 Creating and Executing

CREATE OR ALTER PROCEDURE Sales.usp_GetCustomerOrders
    @CustomerID INT,
    @FromDate   DATE = NULL,              -- optional parameter
    @OrderCount INT OUTPUT                -- output parameter
AS
BEGIN
    SET NOCOUNT ON;

    SELECT o.OrderID, o.OrderDate, o.Status
    FROM dbo.Orders o
    WHERE o.CustomerID = @CustomerID
      AND (@FromDate IS NULL OR o.OrderDate >= @FromDate);

    SET @OrderCount = @@ROWCOUNT;
    RETURN 0;                              -- return code (INT only, status)
END
GO

DECLARE @cnt INT, @rc INT;
EXEC @rc = Sales.usp_GetCustomerOrders @CustomerID = 1, @OrderCount = @cnt OUTPUT;
SELECT @cnt AS Orders, @rc AS ReturnCode;

19.2 Benefits

Plan reuse, reduced network traffic, security (permission on the procedure, not the tables), encapsulation, and protection from SQL injection when parameterized.

19.3 Procedure Options

WITH RECOMPILE: new plan every execution WITH ENCRYPTION

WITH EXECUTE AS OWNER | CALLER | SELF | 'user' WITH NATIVE_COMPILATION, SCHEMABINDING: In-Memory OLTP

OPTION (RECOMPILE) at statement level (usually better than procedure-level recompile)

19.4 Table-Valued Parameters (TVP)

CREATE TYPE dbo.OrderItemType AS TABLE (ProductID INT, Quantity INT, UnitPrice DECIMAL(10,2));
GO
CREATE OR ALTER PROCEDURE dbo.usp_CreateOrder
    @CustomerID INT, @Items dbo.OrderItemType READONLY
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRAN;
        DECLARE @OrderID INT;
        INSERT dbo.Orders (CustomerID) VALUES (@CustomerID);
        SET @OrderID = SCOPE_IDENTITY();
        INSERT dbo.OrderItems (OrderID, ProductID, Quantity, UnitPrice)
        SELECT @OrderID, ProductID, Quantity, UnitPrice FROM @Items;
    COMMIT;
    SELECT @OrderID AS NewOrderID;
END
GO
DECLARE @i dbo.OrderItemType;
INSERT @i VALUES (1, 2, 499.00), (3, 1, 1299.00);
EXEC dbo.usp_CreateOrder @CustomerID = 1, @Items = @i;

19.5 Useful System Stored Procedures

19.6 Procedure Best Practices

Prefix with a schema. Don't use the sp_ prefix (it searches master first).

Use SET NOCOUNT ON and SET XACT_ABORT ON.

Handle errors with TRY/CATCH.

Beware parameter sniffing (Module 35).

Avoid "catch-all" queries (@p IS NULL OR col = @p), or use OPTION (RECOMPILE) or dynamic SQL.