Skip to content

Instantly share code, notes, and snippets.

@suntong

suntong/T-SQL Stored Procedures & UDFs Quick Reference.md

Created July 24, 2025 23:20
Show Gist options
  • Save suntong/df2566b4ca64fc3544372fcd963228d5 to your computer and use it in GitHub Desktop.
Save suntong/df2566b4ca64fc3544372fcd963228d5 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
T-SQL Stored Procedures & UDFs Quick Reference.md

T-SQL Stored Procedures & UDFs: A Comprehensive Quick Reference

This document provides a complete reference for Microsoft SQL Server's Transact-SQL (T-SQL) Stored Procedures and User-Defined Functions (UDFs).

1. Core Concepts: Stored Procedures vs. User-Defined Functions (UDFs)

Both are pre-compiled sets of T-SQL statements stored in the database. They promote code reuse, security, and performance. However, they serve different purposes.

Feature Stored Procedure (SP) User-Defined Function (UDF)
Purpose To perform an action (e.g., insert/update data, run complex business logic, return data). To compute and return a value (either a single scalar value or a table).
Invocation Executed using EXEC or EXECUTE. Called directly within a T-SQL statement (e.g., SELECT, WHERE, FROM).
Side Effects Allowed. Can perform DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER), and change database state. Not allowed. Cannot perform DML or change database state. Read-only.
Transactions Can start and commit/rollback its own transactions. Cannot manage transactions. Runs within the transaction of the calling statement.
Return Value Can return multiple result sets, an integer status code (RETURN), and OUTPUT parameters. Must return a single value: either a scalar type (e.g., INT, NVARCHAR) or a TABLE.

2. Stored Procedures (SPs)

SPs are the workhorses for encapsulating business logic and data modification tasks.

Syntax

Create a Procedure

CREATE PROCEDURE [schema_name.]procedure_name
    -- Parameters (optional)
    @parameter1_name data_type [ = default_value ],
    @parameter2_name data_type [ = default_value ] [ OUTPUT ]
AS
BEGIN
    SET NOCOUNT ON; -- Good practice to prevent sending 'rows affected' messages

    -- T-SQL statements for the procedure
    SELECT CustomerID, CompanyName FROM dbo.Customers WHERE City = @parameter1_name;

    -- Set an output parameter's value
    SET @parameter2_name = @@ROWCOUNT;

    -- Return an integer status code (0 = success, non-zero = failure)
    RETURN 0;
END;

Alter a Procedure

ALTER PROCEDURE [schema_name.]procedure_name
    -- New parameters or logic
AS
BEGIN
    -- ... updated T-SQL statements ...
END;

Execute a Procedure

-- Method 1: By parameter order
EXEC dbo.GetCustomersByCity 'London', @CustomerCount OUTPUT;

-- Method 2: By parameter name (recommended for clarity)
DECLARE @CustomerCount INT;
EXEC dbo.GetCustomersByCity
    @parameter1_name = 'London',
    @parameter2_name = @CustomerCount OUTPUT;

-- Select the output parameter to see its value
SELECT @CustomerCount AS CountOfLondonCustomers;

Drop a Procedure

DROP PROCEDURE [schema_name.]procedure_name;

Usage and Management

  • Return Values: The RETURN statement in an SP is only for an integer status code to indicate success/failure, not for returning data. Data is returned via SELECT statements or OUTPUT parameters.
  • Getting the Code: Use the system stored procedure sp_helptext to view the code of an existing SP. 
    EXEC sp_helptext 'dbo.procedure_name';
  • Recompiling: If underlying table structures change, you might want to force a recompile to get a fresh execution plan. 
    -- For a single execution
EXEC dbo.procedure_name WITH RECOMPILE;

-- To mark the SP to be recompiled every time it runs
ALTER PROCEDURE dbo.procedure_name
WITH RECOMPILE
AS
-- ...
  • Permissions: Users need EXECUTE permission to run an SP. 
    GRANT EXECUTE ON dbo.procedure_name TO user_or_role_name;

3. User-Defined Functions (UDFs)

UDFs are designed to encapsulate calculations or logic that can be reused within queries.

Types of UDFs

  1. Scalar Functions (SVF): Return a single value of a specific data type.
  2. Table-Valued Functions (TVF): Return a result set (a table).
    • Inline Table-Valued Functions (iTVF): Defined by a single SELECT statement. Very performant.
    • Multi-statement Table-Valued Functions (msTVF): Defined by multiple T-SQL statements within a BEGIN...END block. More flexible but can have performance issues.

Scalar Functions

Syntax

CREATE FUNCTION [schema_name.]function_name (@parameter1 data_type)
RETURNS return_data_type -- e.g., INT, NVARCHAR(100), DECIMAL(18,2)
AS
BEGIN
    DECLARE @return_value return_data_type;

    -- Logic to calculate the value
    SET @return_value = @parameter1 * 1.20; -- e.g., calculate price with tax

    RETURN @return_value;
END;

Usage

Scalar functions are used in SELECT lists, WHERE clauses, or anywhere an expression is valid.

SELECT
    ProductID,
    ProductName,
    UnitPrice,
    dbo.CalculatePriceWithTax(UnitPrice) AS PriceWithTax
FROM dbo.Products
WHERE dbo.CalculatePriceWithTax(UnitPrice) > 50;

Table-Valued Functions (TVFs)

These are central to the goal of returning a SELECT result as a reusable object.

Inline Table-Valued Function (iTVF)

This is the most efficient type of UDF. Think of it as a parameterized view.

Syntax:

CREATE FUNCTION [schema_name.]GetProductsByCategory (@CategoryID INT)
RETURNS TABLE
AS
RETURN
(
    -- The function is just a single SELECT statement
    SELECT ProductID, ProductName, UnitPrice
    FROM dbo.Products
    WHERE CategoryID = @CategoryID
);

Usage: You use an iTVF in the FROM clause of a query, just like a table.

SELECT p.ProductName, p.UnitPrice
FROM dbo.GetProductsByCategory(1) AS p; -- CategoryID = 1

-- Can be JOINed
SELECT c.CategoryName, p.ProductName
FROM dbo.Categories AS c
JOIN dbo.GetProductsByCategory(c.CategoryID) AS p ON 1=1; -- No JOIN condition needed for APPLY
-- Or more commonly with CROSS APPLY or OUTER APPLY
SELECT c.CategoryName, p.ProductName
FROM dbo.Categories AS c
CROSS APPLY dbo.GetProductsByCategory(c.CategoryID) AS p;

Multi-statement Table-Valued Function (msTVF)

This provides more flexibility, allowing for intermediate logic before returning the final table.

Syntax:

CREATE FUNCTION [schema_name.]GetTopNProductsInStock (@TopN INT)
RETURNS @ProductList TABLE -- Define the structure of the return table here
(
    ProductID INT,
    ProductName NVARCHAR(100),
    StockLevel INT
)
AS
BEGIN
    -- You can have complex logic, variables, loops, etc. here
    INSERT INTO @ProductList (ProductID, ProductName, StockLevel)
    SELECT TOP (@TopN) ProductID, ProductName, UnitsInStock
    FROM dbo.Products
    WHERE Discontinued = 0
    ORDER BY UnitsInStock DESC;

    -- You could even do more logic here...
    -- UPDATE @ProductList SET ...

    RETURN; -- The populated table variable is returned
END;

Usage: Usage is identical to an iTVF.

SELECT ProductName, StockLevel
FROM dbo.GetTopNProductsInStock(5);

UDF Management & Limitations

  • WITH SCHEMABINDING: Prevents the underlying objects (tables, views) referenced by the function from being altered or dropped. This is often required for indexed views and other features.
  • Determinism: Functions can be deterministic (always return the same result for the same inputs) or non-deterministic (e.g., GETDATE(), NEWID()). Non-deterministic functions have usage restrictions.
  • Performance: Be cautious with scalar functions in WHERE clauses, as they can sometimes prevent the query optimizer from using an index on that column (i.e., they are not "SARGable"). iTVFs are generally very performant, while msTVFs can be slow because the optimizer has poor cardinality estimates for them.
  • Permissions: Users need SELECT permission on a TVF and EXECUTE permission on a scalar function.

4. How to Return Query Results as a Table (SQL Server & General Concepts)

You asked how to create a routine that returns the results of a SELECT ... FROM ... query as a table. In SQL Server, there are three standard ways to accomplish this, each with distinct use cases. These concepts map to similar patterns in other RDBMS like MySQL.

Approach 1: Stored Procedure (The "Action" Method)

This is the simplest way. The SP just executes a SELECT statement as its final action. The client application that calls the SP will receive this as a result set.

Definition:

CREATE PROCEDURE dbo.GetEmployeesByTitle
    @JobTitle NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT EmployeeID, FirstName, LastName, HireDate
    FROM dbo.Employees
    WHERE Title = @JobTitle;
END;

Usage from T-SQL: You cannot JOIN to a stored procedure directly. Its primary use is for an application to call it and process the results. If you need to use the results within another T-SQL batch, you must capture them in a temporary table or table variable.

-- Create a table to hold the results
CREATE TABLE #Engineers (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATETIME
);

-- Execute the SP and insert its results into the temp table
INSERT INTO #Engineers (EmployeeID, FirstName, LastName, HireDate)
EXEC dbo.GetEmployeesByTitle @JobTitle = 'Sales Representative';

-- Now you can query the temp table
SELECT * FROM #Engineers;

DROP TABLE #Engineers;
  • Pros: Can perform actions (logging, updates) before returning data. Can use temp tables inside. Can return multiple, differently structured result sets.
  • Cons: Cannot be composed within another query (e.g., in a FROM or JOIN clause). Requires the INSERT...EXEC pattern for use in T-SQL.

Approach 2: Inline Table-Valued Function (The "Best Performance" Method)

This is the preferred method in SQL Server when you need to parameterize a query and use its results like a table. It's essentially a parameterized, reusable view.

Definition:

CREATE FUNCTION dbo.fn_GetEmployeesByTitle (@JobTitle NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, HireDate
    FROM dbo.Employees
    WHERE Title = @JobTitle
);

Usage from T-SQL: You use it directly in the FROM clause as if it were a real table.

-- Use it directly
SELECT * FROM dbo.fn_GetEmployeesByTitle('Sales Representative');

-- Join it with other tables
SELECT
    e.FirstName,
    e.LastName,
    o.OrderDate,
    o.ShipCity
FROM dbo.fn_GetEmployeesByTitle('Sales Representative') AS e
JOIN dbo.Orders AS o ON e.EmployeeID = o.EmployeeID
WHERE o.ShipCity = 'London';
  • Pros: Excellent performance. The query optimizer can "see through" the function and create an optimal plan that incorporates the underlying tables. Directly composable in queries.
  • Cons: Limited to a single SELECT statement. No procedural logic or side effects allowed.

Approach 3: Multi-statement Table-Valued Function (The "Flexible" Method)

Use this when you need procedural logic (e.g., loops, multiple steps, complex conditions) to build the result set before returning it.

Definition:

CREATE FUNCTION dbo.fn_GetEmployeesAndInternsByTitle (@JobTitle NVARCHAR(50))
RETURNS @Results TABLE (
    PersonID INT,
    FullName NVARCHAR(101),
    HireDate DATETIME,
    PersonType VARCHAR(10)
)
AS
BEGIN
    -- Step 1: Insert full-time employees
    INSERT INTO @Results (PersonID, FullName, HireDate, PersonType)
    SELECT EmployeeID, FirstName + ' ' + LastName, HireDate, 'Employee'
    FROM dbo.Employees
    WHERE Title = @JobTitle;

    -- Step 2: Insert interns (hypothetical table)
    IF @JobTitle = 'Developer'
    BEGIN
        INSERT INTO @Results (PersonID, FullName, HireDate, PersonType)
        SELECT InternID, FullName, StartDate, 'Intern'
        FROM dbo.Interns
        WHERE Department = 'IT';
    END

    RETURN;
END;

Usage from T-SQL: The usage is the same as an iTVF.

SELECT * FROM dbo.fn_GetEmployeesAndInternsByTitle('Developer');
  • Pros: Very flexible. Allows for complex procedural logic to build the result set. Is composable in queries.
  • Cons: Potential for poor performance. The optimizer treats the function as a "black box" and often uses a fixed, small estimate for the number of rows it will return. This can lead to very bad execution plans, especially when joining to large tables. Use with caution and test performance carefully.

Note on "MySQL Approaches"

While MySQL has its own syntax for procedures and functions, the concepts map directly to the SQL Server approaches above.

  • A MySQL procedure that ends with a SELECT is equivalent to Approach 1 (T-SQL Stored Procedure).
  • A MySQL function that returns a complex result is best implemented in T-SQL using Approach 2 (iTVF) for performance, or Approach 3 (msTVF) if complex internal logic is required.

The most idiomatic and performant way to create a reusable, parameterized "table-like" query in MS SQL Server is the Inline Table-Valued Function (iTVF).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment