Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 39 ยท Procedures, Triggers and Functions

Stored Procedures in SQL Server

Create and call SQL Server stored procedures with parameters, OUTPUT values, return codes, default values and error handling. Learn plans, permissions and best practices.

A stored procedure is a named, saved batch of T-SQL that lives in the database. Applications call it by name and pass parameters instead of sending long SQL text. This gives you less network traffic, plan reuse, one place to change logic, protection from SQL injection when parameters are used properly, and a clean security boundary: users can be allowed to run a procedure without having rights on the tables behind it.

You've met the same idea in the SQL stored procedures lesson. This one shows SQL Server's syntax and conventions. Procedures can't run in the browser, so the T-SQL is read-only and a runnable stand-in follows.

Creating and calling one

SQL
CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId int,
    @Since      date = NULL          -- optional, with a default
AS
BEGIN
    SET NOCOUNT ON;

    SELECT o.OrderId, o.OrderDate, o.Total
    FROM   dbo.Orders AS o
    WHERE  o.CustomerId = @CustomerId
      AND  (@Since IS NULL OR o.OrderDate >= @Since)
    ORDER  BY o.OrderDate DESC;
END;
GO

EXEC dbo.GetCustomerOrders @CustomerId = 42;
EXEC dbo.GetCustomerOrders @CustomerId = 42, @Since = '2025-01-01';

Points worth knowing:

  • CREATE OR ALTER (SQL Server 2016 SP1 and later) replaces the old drop-and-recreate dance.
  • Name parameters when you call (@CustomerId = 42); positional calls break when parameters change.
  • SET NOCOUNT ON stops the "N rows affected" messages that otherwise add network chatter.
  • Use the dbo. schema prefix. It avoids ambiguity and helps plan reuse. Don't start names with sp_: SQL Server searches the master database for such names first.
  • A procedure can return result sets (any SELECT that isn't assigned), output parameters and an integer return code.

OUTPUT parameters and return codes

SQL
CREATE OR ALTER PROCEDURE dbo.AddOrder
    @CustomerId int,
    @Total      decimal(10,2),
    @NewOrderId int OUTPUT
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

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

    INSERT INTO dbo.Orders (CustomerId, OrderDate, Total)
    VALUES (@CustomerId, SYSDATETIME(), @Total);

    SET @NewOrderId = SCOPE_IDENTITY();
    RETURN 0;
END;
GO

DECLARE @Id int, @rc int;
EXEC @rc = dbo.AddOrder @CustomerId = 42, @Total = 99.90, @NewOrderId = @Id OUTPUT;
SELECT @rc AS ReturnCode, @Id AS NewOrderId;

Use OUTPUT parameters for values (like new IDs), and return codes only for status (0 = success by convention). For data, return a result set. To capture a procedure's result set in a table: INSERT INTO #t EXEC dbo.GetCustomerOrders @CustomerId = 42;.

Error handling inside a procedure

Combine everything from the transactions lesson:

SQL
CREATE OR ALTER PROCEDURE dbo.TransferFunds
    @From int, @To int, @Amount decimal(12,2)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE dbo.Accounts SET Balance -= @Amount WHERE AccountId = @From;
        IF @@ROWCOUNT = 0 THROW 50001, 'Source account not found', 1;
        UPDATE dbo.Accounts SET Balance += @Amount WHERE AccountId = @To;
        IF @@ROWCOUNT = 0 THROW 50002, 'Target account not found', 1;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH;
END;

A CHECK (Balance >= 0) constraint on the table then rejects overdrafts, and the procedure rolls everything back.

A runnable stand-in

SQLite can't create procedures, but you can express the same logic as a short script whose "parameters" live in a table. The transfer below touches two rows and then shows both balances:

SQL Transfer logic as a script (stand-in for a procedure)

Press Run (or Ctrl + Enter) to see the result here.

The CHECK constraint is the safety net. Change 150 to 900 and run it: the first UPDATE fails and nothing changes.

SQL The constraint rejects an overdraft

Press Run (or Ctrl + Enter) to see the result here.

The error text appears in the output panel, just as an SQL Server client would show error 547 for a violated constraint.

Procedure performance notes

  • Plan caching: SQL Server compiles a plan on the first run and reuses it. A plan built for one parameter value can be slow for another: this is parameter sniffing. Fixes include OPTION (RECOMPILE), OPTIMIZE FOR, better indexes or splitting the procedure; see execution plans.
  • Optional-filter ("catch-all") queries like (@Since IS NULL OR ...) are convenient but can produce poor plans. For big tables use OPTION (RECOMPILE) or dynamic SQL.
  • Table-valued parameters pass a whole set of rows: define CREATE TYPE dbo.IdList AS TABLE (Id int), then declare the parameter @Ids dbo.IdList READONLY.
  • Permissions: GRANT EXECUTE ON dbo.AddOrder TO AppRole; is enough; ownership chaining lets the procedure touch tables the caller can't (see security).
  • EXECUTE AS lets a procedure run under a different security context.

Show the source with EXEC sp_helptext 'dbo.AddOrder' or SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.AddOrder')).

sequenceDiagram
  participant App
  participant SQL as SQL Server
  App->>SQL: EXEC dbo.AddOrder @CustomerId = 42
  SQL->>SQL: Reuse cached plan or compile
  SQL->>SQL: Run statements in a transaction
  SQL-->>App: Result set, OUTPUT values, return code

One round trip replaces many separate statements.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should you avoid naming your own procedures with the sp_ prefix?

    Names starting with sp_ trigger a lookup in the master database before the current database.
  2. 2. Which is the conventional use of a procedure's RETURN value?

    Return codes are integers meant for status. Use OUTPUT parameters for values and SELECT for rows.
  3. 3. What does SET NOCOUNT ON do?

    It removes the DONE_IN_PROC messages, reducing network traffic.
  4. 4. What is parameter sniffing?

    The cached plan is optimized for the sniffed values; that can be a problem when later values have very different row counts.

Summary

  • CREATE OR ALTER PROCEDURE defines a named batch; call it with EXEC and named parameters.
  • Return rows with SELECT, values with OUTPUT parameters and status with RETURN.
  • Start write procedures with SET NOCOUNT, XACT_ABORT ON and wrap work in TRY...CATCH.
  • Watch for parameter sniffing and catch-all filters.

Next: User-defined functions