<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Programmability
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 38 of 54 17 minAdvanced

Stored procedures and parameters

Save T-SQL as a procedure and call it with EXEC: input parameters, defaults, named and positional calls, OUTPUT parameters and the forgotten-OUTPUT trap, return codes, checking input with THROW, and the missing-GO trap.

A stored procedure is a named block of T-SQL saved in the database. Applications call procedures instead of sending SQL text: the code lives in one place, it can take parameters, and users can be allowed to run it without any permission on the tables it touches.

Query.sql
CREATE OR ALTER PROCEDURE dbo.GetProductsByCategory    @CategoryId intASBEGIN    SET NOCOUNT ON;     SELECT ProductId, Name, Price, Stock    FROM dbo.Products    WHERE CategoryId = @CategoryId    ORDER BY Price DESC;END;GOEXEC dbo.GetProductsByCategory @CategoryId = 1;
Output
ProductId  Name              Price  Stock
---------  ----------------  -----  -----
        2  Cordless drill    89.00     12
        1  Claw hammer       18.50     40
        3  Tape measure 8 m   9.75     65

SET NOCOUNT ON stops the “(3 rows affected)” message after each statement. Those messages are extra network traffic and can confuse some client libraries, so it is the usual first line of a procedure.

Calling with parameters

You can pass parameters by name or by position. Named calls are clearer and keep working if someone adds a parameter later. A parameter with no default must be given:

Query.sql
EXEC dbo.GetProductsByCategory 4;                -- by positionEXEC dbo.GetProductsByCategory @CategoryId = 6;  -- by nameEXEC dbo.GetProductsByCategory;                  -- missing
Output
ProductId  Name               Price  Stock
---------  -----------------  -----  -----
        8  Wood screws (200)  11.90    150
        9  Wall plugs (100)    4.50      0

ProductId  Name            Price  Stock
---------  --------------  -----  -----
       12  Safety goggles   6.80      0

Msg 201, Level 16, State 4, Procedure dbo.GetProductsByCategory, Line 0
Procedure or function 'GetProductsByCategory' expects parameter '@CategoryId', which was not supplied.

Defaults

Give a parameter a default and callers can leave it out. A NULL default is the usual way to make a filter optional:

Query.sql
CREATE OR ALTER PROCEDURE dbo.GetProductsByCategory    @CategoryId int,    @MaxPrice   decimal(10,2) = NULL     -- optionalASBEGIN    SET NOCOUNT ON;     SELECT ProductId, Name, Price, Stock    FROM dbo.Products    WHERE CategoryId = @CategoryId      AND (@MaxPrice IS NULL OR Price <= @MaxPrice)    ORDER BY Price DESC;END;GOEXEC dbo.GetProductsByCategory @CategoryId = 1;EXEC dbo.GetProductsByCategory @CategoryId = 1, @MaxPrice = 20;
Output
ProductId  Name              Price  Stock
---------  ----------------  -----  -----
        2  Cordless drill    89.00     12
        1  Claw hammer       18.50     40
        3  Tape measure 8 m   9.75     65

ProductId  Name              Price  Stock
---------  ----------------  -----  -----
        1  Claw hammer       18.50     40
        3  Tape measure 8 m   9.75     65

OUTPUT parameters

A procedure can return values through parameters marked OUTPUT. This one adds stock and hands back the new level. The caller must say OUTPUT too:

Query.sql
CREATE OR ALTER PROCEDURE dbo.AddStock    @ProductId int,    @Qty       int = 1,    @NewStock  int OUTPUTASBEGIN    SET NOCOUNT ON;     UPDATE dbo.Products    SET Stock = Stock + @Qty    WHERE ProductId = @ProductId;     SELECT @NewStock = Stock FROM dbo.Products WHERE ProductId = @ProductId;END;GODECLARE @Stock int; EXEC dbo.AddStock @ProductId = 9, @Qty = 50, @NewStock = @Stock OUTPUT;SELECT @Stock AS WithOutput; EXEC dbo.AddStock @ProductId = 9, @Qty = 50, @NewStock = @Stock;SELECT @Stock AS WithoutOutput,       (SELECT Stock FROM dbo.Products WHERE ProductId = 9) AS RealStock;
Output
WithOutput
----------
        50

WithoutOutput  RealStock
-------------  ---------
           50        100

Look closely at the second call. Without the word OUTPUT at the call, the value is not copied back. There is no error and no warning: the variable keeps whatever it held before (50), while the real stock moved on to 100.

Return codes and checking input

RETURN n ends a procedure and hands back an integer status. By convention 0 means success. For a real error, raise one with THROW: it stops the procedure and the caller sees the message (the Error handling lesson covers THROW in full).

Query.sql
CREATE OR ALTER PROCEDURE dbo.AddStock    @ProductId int,    @Qty       int = 1,    @NewStock  int OUTPUTASBEGIN    SET NOCOUNT ON;     IF @Qty <= 0        THROW 50001, N'Quantity must be at least 1.', 1;     IF NOT EXISTS (SELECT 1 FROM dbo.Products WHERE ProductId = @ProductId)        RETURN 1;                        -- no such product     UPDATE dbo.Products    SET Stock = Stock + @Qty    WHERE ProductId = @ProductId;     SELECT @NewStock = Stock FROM dbo.Products WHERE ProductId = @ProductId;    RETURN 0;END;GODECLARE @rc int, @Stock int; EXEC @rc = dbo.AddStock @ProductId = 99, @Qty = 5, @NewStock = @Stock OUTPUT;SELECT @rc AS ReturnCode, @Stock AS NewStock; EXEC @rc = dbo.AddStock @ProductId = 12, @NewStock = @Stock OUTPUT;   -- @Qty defaults to 1SELECT @rc AS ReturnCode, @Stock AS NewStock; EXEC @rc = dbo.AddStock 12, -5, @Stock OUTPUT;
Output
ReturnCode  NewStock
----------  --------
         1      NULL

ReturnCode  NewStock
----------  --------
         0         1

Msg 50001, Level 16, State 1, Procedure dbo.AddStock, Line 10
Quantity must be at least 1.

In a positional call you cannot just skip a parameter in the middle. Write the keyword DEFAULT in its place: EXEC dbo.AddStock 12, DEFAULT, @Stock OUTPUT;.

Interactive

Calling dbo.AddStock

dbo.AddStock @ProductId int, @Qty int = 1, @NewStock int OUTPUT
@ProductId
@Qty
DECLARE @rc int, @Stock int;

EXEC @rc = dbo.AddStock @ProductId = 9, @Qty = 5, @NewStock = @Stock OUTPUT;

SELECT @rc AS ReturnCode, @Stock AS NewStock,
       (SELECT Stock FROM dbo.Products WHERE ProductId = 9) AS RealStock;
What SSMS shows
ReturnCodeNewStockRealStock
1055
Success: RETURN 0, and OUTPUT copied the new stock back into @Stock.

Saving results from a procedure

A procedure’s result set can be captured with INSERT … EXEC. The table must have matching columns:

Query.sql
CREATE TABLE #Tools (ProductId int, Name nvarchar(60), Price decimal(10,2), Stock int); INSERT INTO #ToolsEXEC dbo.GetProductsByCategory @CategoryId = 1; SELECT COUNT(*) AS Tools, SUM(Price * Stock) AS StockValue FROM #Tools;DROP TABLE #Tools;
Output
Tools  StockValue
-----  ----------
    3     2441.75

The missing GO trap

A procedure’s body runs to the end of the batch, not to the end of the BEGIN … END block. Forget the GO after a procedure and the test lines below it become part of the procedure:

Query.sql
CREATE OR ALTER PROCEDURE dbo.CountOrdersAS    SELECT COUNT(*) AS Orders FROM dbo.Orders; SELECT TOP (2) OrderId, Status FROM dbo.Orders ORDER BY OrderId;   -- meant as a testGOEXEC dbo.CountOrders;
Output
Orders
------
    10

OrderId  Status
-------  -------
      1  Shipped
      2  Shipped

The CREATE ran, but the test SELECT did not run: it was saved inside the procedure, so every call now returns two result sets. If the test line had been EXEC dbo.CountOrders;, the procedure would call itself until SQL Server stopped it at 32 levels deep (Msg 217). Always put GO straight after a procedure.

Quick check

A procedure has @Total money OUTPUT. The caller runs EXEC dbo.CalcTotal @Total = @t;. What is in @t afterwards?

Quick check

Which call is wrong for dbo.AddStock @ProductId int, @Qty int = 1, @NewStock int OUTPUT?

Practice

Write a procedure dbo.CustomerOrders that takes @CustomerId and an optional @Status (default NULL, meaning all statuses) and returns that customer’s OrderId, OrderDate and Status, oldest first. Call it for customer 4 with no status, then for customer 2 with status Pending.

Show a hint

Use the same optional-filter pattern as @MaxPrice: (@Status IS NULL OR Status = @Status). Put GO after the procedure, before the calls.

Show the solution
Solution.sql
CREATE OR ALTER PROCEDURE dbo.CustomerOrders    @CustomerId int,    @Status     varchar(10) = NULLASBEGIN    SET NOCOUNT ON;     SELECT OrderId, OrderDate, Status    FROM dbo.Orders    WHERE CustomerId = @CustomerId      AND (@Status IS NULL OR Status = @Status)    ORDER BY OrderDate;END;GOEXEC dbo.CustomerOrders @CustomerId = 4;EXEC dbo.CustomerOrders @CustomerId = 2, @Status = 'Pending';
Output
OrderId  OrderDate   Status
-------  ----------  -------
      4  2024-03-15  Shipped
      7  2024-05-05  Shipped

OrderId  OrderDate   Status
-------  ----------  -------
      9  2024-06-01  Pending

Remember

  • A stored procedure is named, saved T-SQL; call it with EXEC and parameters.
  • Start procedures with SET NOCOUNT ON; use CREATE OR ALTER so scripts can be rerun.
  • Pass parameters by name for clarity; a parameter without a default must be supplied (Msg 201).
  • A NULL default plus (@p IS NULL OR col = @p) makes a filter optional.
  • OUTPUT is needed in both the definition and the call; forget it at the call and the value is silently lost.
  • RETURN gives an integer status (0 = success); THROW raises a real error.
  • INSERT … EXEC captures a procedure’s result set into a table.
  • A procedure runs to the end of its batch: always put GO after it.
0 of 54 lessons completed