SQLMentor // learn sql server

Stored Procedures

A stored procedure is a named, compiled T-SQL routine stored in a database. Procs accept parameters (IN and OUTPUT), return rowsets, support transactions and error handling, and can be granted EXECUTE permission separately from the underlying tables.

Creating a Procedure

CREATE OR ALTER PROCEDURE hr.sp_get_employee
    @employee_id INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  employee_id, first_name, last_name, email, salary, department_id
    FROM    hr.employees
    WHERE   employee_id = @employee_id;
END;
GO

CREATE OR ALTER (SQL Server 2016+) idempotently creates or updates the proc — much friendlier than the older IF OBJECT_ID(...) DROP / CREATE pattern.

Calling Procedures

EXEC hr.sp_get_employee 100;                     -- positional
EXEC hr.sp_get_employee @employee_id = 100;      -- named (preferred)
EXECUTE hr.sp_get_employee @employee_id = 100;   -- EXECUTE = EXEC

-- Without EXEC (only when first statement in a batch — error-prone, avoid)
hr.sp_get_employee 100;

Parameters

IN Parameters with Defaults

CREATE OR ALTER PROCEDURE hr.sp_give_raise
    @employee_id INT,
    @pct         DECIMAL(5,2) = 5.00,           -- default 5%
    @reason      NVARCHAR(100) = N'Annual review'
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    UPDATE hr.employees
    SET    salary = ROUND(salary * (1 + @pct / 100.0), 2)
    WHERE  employee_id = @employee_id;

    IF @@ROWCOUNT = 0
        THROW 50001, 'Employee not found', 1;
END;
GO

-- Use defaults
EXEC hr.sp_give_raise @employee_id = 100;
-- Override one
EXEC hr.sp_give_raise @employee_id = 100, @pct = 10;
-- Skip middle parameter — must use named for any after a skip
EXEC hr.sp_give_raise @employee_id = 100, @reason = N'Promotion';

OUTPUT Parameters

CREATE OR ALTER PROCEDURE hr.sp_get_emp_summary
    @employee_id INT,
    @full_name   NVARCHAR(100) OUTPUT,
    @department  NVARCHAR(100) OUTPUT,
    @salary      DECIMAL(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  @full_name  = e.first_name + N' ' + e.last_name,
            @department = d.department_name,
            @salary     = e.salary
    FROM    hr.employees   e
    LEFT JOIN hr.departments d ON d.department_id = e.department_id
    WHERE   e.employee_id = @employee_id;
END;
GO

DECLARE @name NVARCHAR(100), @dept NVARCHAR(100), @sal DECIMAL(10,2);

EXEC hr.sp_get_emp_summary
    @employee_id = 100,
    @full_name   = @name OUTPUT,    -- OUTPUT keyword required at call site
    @department  = @dept OUTPUT,
    @salary      = @sal  OUTPUT;

PRINT @name + ' / ' + @dept + ' / ' + CAST(@sal AS NVARCHAR(20));

RETURN vs OUTPUT vs Result Sets

A procedure has three distinct ways to send data back:

Mechanism Type Best for
Result set (SELECT) Rows Lookups, lists, reports
OUTPUT parameter Single scalar values Counts, ids, status flags
RETURN code Single integer Success/failure flags only 
CREATE OR ALTER PROCEDURE hr.sp_check_email
    @email NVARCHAR(100),
    @id    INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @id = employee_id FROM hr.employees WHERE email = @email;

    IF @id IS NULL
        RETURN 1;       -- 1 = not found
    RETURN 0;           -- 0 = success
END;
GO

DECLARE @rc INT, @id INT;
EXEC @rc = hr.sp_check_email @email = N'sking@example.com', @id = @id OUTPUT;
SELECT @rc AS return_code, @id AS employee_id;

RETURN only carries an integer — historically used for status codes. Modern style favors raising errors with THROW over magic return codes.

Returning Rowsets

The most common pattern — just SELECT:

CREATE OR ALTER PROCEDURE hr.sp_emps_by_department
    @department_id INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  employee_id, first_name, last_name, salary, hire_date
    FROM    hr.employees
    WHERE   department_id = @department_id
    ORDER BY salary DESC;
END;
GO

EXEC hr.sp_emps_by_department @department_id = 50;

Multiple SELECTs in a proc return multiple result sets — the client can iterate them.

EXECUTE WITH RECOMPILE

Force a fresh plan on each call (avoids parameter sniffing for one-off use):

EXEC hr.sp_emps_by_department @department_id = 50 WITH RECOMPILE;

-- Or compile the whole proc fresh every time
CREATE OR ALTER PROCEDURE hr.sp_volatile
    @flag INT
WITH RECOMPILE
AS
    SELECT * FROM hr.orders WHERE flag = @flag;
GO

Dynamic SQL: EXEC vs sp_executesql

When the SQL text is built at runtime, you have two execution choices:

DECLARE @col NVARCHAR(50) = N'salary';
DECLARE @sql NVARCHAR(MAX);

-- Bad: string concatenation, no parameterisation, plan-cache pollution
SET @sql = N'SELECT ' + @col + N' FROM hr.employees WHERE department_id = 50;';
EXEC (@sql);

-- Better: sp_executesql with parameter placeholders
SET @sql = N'SELECT ' + QUOTENAME(@col) + N'
             FROM hr.employees
             WHERE department_id = @dept;';
EXEC sp_executesql @sql,
     N'@dept INT',                  -- parameter declaration string
     @dept = 50;
Feature EXEC (@sql) sp_executesql
Parameterisation No Yes
Plan caching Each call may build new plan Reused for same shape
SQL injection safety Manual quoting Built-in for params
Identifier substitution Concatenation only Concatenation + QUOTENAME

sp_executesql is almost always the right choice. Use QUOTENAME() for object names you can't pass as parameters (table/column names).

Table-Valued Parameters (TVPs)

Pass a whole rowset to a procedure — cleaner than CSV strings or temp tables:

CREATE TYPE hr.IntList AS TABLE (id INT PRIMARY KEY);
GO

CREATE OR ALTER PROCEDURE hr.sp_get_employees_by_ids
    @ids hr.IntList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT  e.*
    FROM    hr.employees e
    JOIN    @ids         i ON i.id = e.employee_id;
END;
GO

DECLARE @ids hr.IntList;
INSERT INTO @ids (id) VALUES (100), (101), (102);

EXEC hr.sp_get_employees_by_ids @ids = @ids;

TVPs are always READONLY — you cannot update them inside the proc.

Inspecting and Managing Procedures

-- View source
EXEC sp_helptext 'hr.sp_give_raise';

-- List all procs in a schema
SELECT name, create_date, modify_date
FROM   sys.procedures
WHERE  SCHEMA_NAME(schema_id) = 'hr'
ORDER BY name;

-- Drop
DROP PROCEDURE hr.sp_give_raise;

-- Permissions
GRANT EXECUTE ON hr.sp_give_raise TO hr_app_role;
REVOKE EXECUTE ON hr.sp_give_raise FROM hr_app_role;

Best Practices

  • Begin every proc with SET NOCOUNT ON; SET XACT_ABORT ON;.
  • Use CREATE OR ALTER for idempotent deployments.
  • Prefer named parameters at the call site for readability and resilience to reordering.
  • Use sp_executesql with parameters for any dynamic SQL.
  • Use TVPs (table-valued parameters) instead of comma-delimited string lists.
  • Don't use RETURN for anything other than a small status integer; raise errors with THROW.

Summary

  • Stored procedures are named T-SQL routines — CREATE OR ALTER PROCEDURE name AS BEGIN ... END;
  • Parameters are IN by default, OUTPUT for two-way; defaults make params optional.
  • Three return mechanisms: rowsets (SELECT), OUTPUT parameters, integer RETURN code.
  • sp_executesql parameterises dynamic SQL for plan reuse and safety.
  • TVPs (CREATE TYPE ... AS TABLE) are the clean way to pass rowsets in.