Stored Procedures

Every morning the analyst types the same "top batters above X runs" query, changing only the number. A stored procedure saves that query (and any logic around it) in the database under a name, so anyone can run it with one line and a parameter.

Definition

A stored procedure is a named, saved program of one or more SQL statements, stored in the database and executed on demand with EXEC. It can accept input parameters, return result sets or output values, and contain procedural logic such as variables, IF … ELSE, loops and TRY … CATCH error handling.

• Named and saved — created once with CREATE PROCEDURE, it stays in the database until dropped.
• Executed on demand — nothing happens until someone runs EXEC procedure_name (unlike a trigger).
• Parameters — values like @MinRuns make one procedure reusable for many situations.
• Procedural logic — T-SQL adds programming features that a plain SELECT doesn't have.

Note: Stored procedures are a SQL Server (T-SQL) feature here. SQLite (the playground) has no stored procedures, so these examples are shown as code to run in SSMS. MySQL and PostgreSQL have them too, with different syntax.

Syntax

CREATE PROCEDURE procedure_name
    @param1 DATA_TYPE,
    @param2 DATA_TYPE = default_value      -- optional parameter
AS
BEGIN
    SET NOCOUNT ON;                         -- hide "n rows affected" messages
    -- SQL statements using @param1, @param2
END;

EXEC procedure_name @param1 = value1, @param2 = value2;

ALTER PROCEDURE procedure_name ...;        -- change it
DROP PROCEDURE procedure_name;             -- remove it

Example — GetTopBatters with a parameter

CREATE PROCEDURE GetTopBatters
    @MinRuns INT
AS
BEGIN
    SELECT name, runs
    FROM players
    WHERE runs >= @MinRuns
    ORDER BY runs DESC;
END;

EXEC GetTopBatters @MinRuns = 700;

Run it again with @MinRuns = 500 and you get 4 players — same procedure, different input. Parameters make it flexible.

Stored procedure vs plain query vs Python

Other benefits: security (users can be allowed to EXEC a procedure without direct access to the tables), less network traffic (one EXEC line instead of many statements), and plan reuse (SQL Server caches the execution plan).

Flow control — IF / ELSE and variables

CREATE PROCEDURE AddRuns
    @PlayerId INT,
    @Runs     INT = NULL
AS
BEGIN
    IF @Runs IS NULL
        SET @Runs = 0;                       -- no value given? use 0

    IF @Runs < 0
    BEGIN
        PRINT 'Runs cannot be negative';
        RETURN;                              -- stop the procedure
    END
    ELSE
    BEGIN
        UPDATE players
        SET runs = runs + @Runs
        WHERE id = @PlayerId;
    END
END;

EXEC AddRuns @PlayerId = 4, @Runs = 50;     -- Dhoni 350 -> 400

IF … ELSE decides which step runs. DECLARE @var creates a local variable, SET assigns it, and BEGIN … END groups several statements under one IF.

Error handling — TRY … CATCH

CREATE PROCEDURE SafeUpdate
AS
BEGIN
    BEGIN TRY
        UPDATE players
        SET runs = runs / 0;                 -- may fail
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER()  AS error_no,     -- 8134
            ERROR_MESSAGE() AS problem;      -- 'Divide by zero error encountered.'
    END CATCH
END;

Common mistakes

• ❌ Forgetting @ on parameters (MinRuns INT). ✅ @MinRuns INT.
• ❌ EXEC GetTopBatters with no value when the parameter has no default → error "expects parameter '@MinRuns'". ✅ pass it, or give it a default.
• ❌ Running CREATE PROCEDURE twice → "already an object named…". ✅ ALTER PROCEDURE or CREATE OR ALTER PROCEDURE (2016 SP1+).
• ❌ Building SQL by gluing user text into a string → SQL injection. ✅ use parameters.
• ❌ Naming your procedures sp_… → SQL Server first searches the master database for sp_ names. ✅ use usp_ or no prefix.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: Stored procedure vs function? A procedure is run with EXEC, can change data (INSERT/UPDATE/DELETE), can return several result sets and use TRY…CATCH. A user-defined function is used inside a query (SELECT dbo.fn(x)), must return a value or a table, and cannot modify table data.