Lesson 13 — Programmability

Stored Procedures

A stored procedure is a saved, named block of T-SQL code that can accept parameters and be executed repeatedly, encapsulating logic directly inside the database.

Creating and Executing a Procedure

A stored procedure can accept input parameters, run one or more statements, and optionally return data — making it reusable across many applications without duplicating the logic in each one.

T-SQL — A parameterised stored procedure 
CREATE PROCEDURE GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    SELECT OrderId, OrderDate, Total
    FROM Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;

-- Executing it:
EXEC GetOrdersByCustomer @CustomerId = 42;

Why Use Stored Procedures?

Stored procedures centralise business logic in the database, can improve performance through query plan reuse, and reduce the surface area for SQL injection when used correctly with parameters instead of concatenated strings.

Common Mistakes

  • Building dynamic SQL by concatenating parameter values directly into a query string instead of using parameters, opening the door to SQL injection.
  • Writing overly large procedures that try to do too much, making them hard to test and maintain.
  • Forgetting to handle the case where a parameter might be NULL when the logic doesn't expect it.
  • Not using consistent naming conventions across procedures, making a growing database harder to navigate.

Professional Tip

Always use parameters (like @CustomerId) rather than building queries by concatenating user input into a string. Parameters are automatically escaped by SQL Server, which is the single most effective defence against SQL injection.

Your Turn

Write a stored procedure called AddNewCustomer that accepts first name, last name, and email as parameters and inserts a new row into the Customers table.

Mini Quiz

What is one major security benefit of using parameters in a stored procedure instead of concatenating input into a query string?