SpDev Install free

What is a stored procedure in SQL Server? A practical introduction

What a stored procedure is, how to create and run one, what it's good for and where it hurts, with examples you can run on SQL Server.

By Areeb Ahmer, . 5 minute read.

A stored procedure is a named block of T-SQL saved in the database. You call it by name, pass it parameters, and it runs its statements on the server: queries, inserts and updates, loops, transactions, calls to other procedures. It can return rows, output parameters and a status code.

If you write .NET or Java against SQL Server, you have almost certainly called one, even if your ORM hid it. This article explains what they are, how to write and run one, and when they help.

The examples run on any SQL Server since 2016, against the SpDevDemo database.

A first stored procedure

CREATE OR ALTER PROCEDURE dbo.GetOpenOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderNumber, OrderDate, Total
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
      AND Status = 'Open'
    ORDER BY OrderDate DESC;
END

The parts:

  • CREATE OR ALTER PROCEDURE creates the procedure, or replaces it if it exists (SQL Server 2016 SP1 and later; before that, CREATE then ALTER).
  • dbo.GetOpenOrders is its name, with its schema. Always write the schema.
  • @CustomerId INT is a parameter. A procedure can have up to 2,100.
  • SET NOCOUNT ON stops the “(n rows affected)” message after each statement, which clients don’t need.
  • The body between BEGIN and END is ordinary T-SQL.

Run it with EXEC:

EXEC dbo.GetOpenOrders @CustomerId = 42;

It returns customer 42’s three open orders as a result set, exactly as if you had run the SELECT yourself.

What a procedure can return

A procedure has three ways to hand data back, and can use all three at once:

CREATE OR ALTER PROCEDURE dbo.CustomerSummary
    @CustomerId  INT,
    @OrderCount  INT            OUTPUT,
    @TotalSpent  DECIMAL(14, 2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerId = @CustomerId)
        RETURN 1;                                   -- status code: not found

    SELECT @OrderCount = COUNT(*), @TotalSpent = SUM(Total)
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;

    SELECT TOP (5) OrderNumber, OrderDate, Total     -- a result set
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;

    RETURN 0;                                       -- status code: success
END
GO

DECLARE @status INT, @count INT, @spent DECIMAL(14, 2);
EXEC @status = dbo.CustomerSummary @CustomerId = 42, @OrderCount = @count OUTPUT, @TotalSpent = @spent OUTPUT;
SELECT @status AS Status, @count AS OrderCount, @spent AS TotalSpent;
  • Result sets come from SELECT statements, any number of them.
  • Output parameters return single values. The caller must write OUTPUT too.
  • The return code is an integer, by convention 0 for success. Use it for status, not for data.

There is a whole article on output parameters, return values and result sets.

Why use stored procedures

One round trip for a lot of work. A procedure can read, decide, update three tables and log the result in one call. From an application, the same logic might be a dozen round trips, each paying network latency.

A stable interface to the data. Applications call dbo.PlaceOrder; how orders are stored can change behind it. Several applications in different languages can share the same logic.

Security. You can grant EXECUTE on a procedure without granting any rights on the tables it uses. Users can then change data only in the ways the procedures allow. Because parameters are typed values, not text spliced into SQL, a procedure that doesn’t build dynamic SQL from its inputs isn’t open to SQL injection through them.

Cached plans. The plan for each statement in a procedure is compiled on first use and reused. Parameterized queries from an application get the same benefit, so this is less of an advantage than it once was, and it’s also the source of parameter sniffing.

Transactions near the data. A procedure can wrap several changes in one transaction and keep it short, instead of holding locks while an application makes several calls.

Where they hurt

Tooling. C# has debuggers, profilers, refactoring and unit test runners. T-SQL in SSMS has none of those: the T-SQL debugger was removed from SSMS after version 17.9. Debugging usually means PRINT statements. That’s the gap SpDev was built to close.

Versioning and deployment. A procedure lives in the database, not in your build. Keep every procedure in source control as a .sql file, and deploy it with your schema migrations, or the database becomes the only copy.

Business logic in two places. When some rules are in C# and some in procedures, nobody is sure where to look. Decide which kind of logic goes where, and keep to it.

Testing. Procedures can be unit tested (tSQLt is the common framework), but it takes more setup than testing application code.

Stored procedure, function or view?

All three are named T-SQL saved in the database, but they do different jobs:

Stored procedure Function View
Called with EXEC Inside a query Inside a query, like a table
Parameters Yes, including output Input only No
Can change data Yes No Through it, with limits
Transactions, TRY…CATCH, dynamic SQL Yes No No
Can be joined to No Table-valued ones, yes Yes

The detailed comparisons: stored procedure vs function and stored procedure vs view.

Finding the procedures in a database

SELECT s.name AS schema_name, p.name AS procedure_name, p.create_date, p.modify_date
FROM sys.procedures AS p
JOIN sys.schemas AS s ON s.schema_id = p.schema_id
ORDER BY s.name, p.name;

To read one’s code, use sp_helptext 'dbo.GetOpenOrders' or SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.GetOpenOrders')). To search all of them for a table or a phrase, see how to find text in stored procedures.

Next steps