Skip to content

T-SQL functions and stored procedures

T-SQL ships with hundreds of built-in functions, and you can add your own as user-defined functions or wrap whole operations in stored procedures. This page covers the built-in functions you reach for every day (text, dates and type conversion) and then the two ways to package your own logic in the database. Aggregate functions such as SUM and COUNT are on Querying data.

Version notes in each section say when a function arrived, because many teams still run SQL Server 2019 or 2022 next to SQL Server 2025 (17.x). Azure SQL Database usually has the newest functions first. The samples use the OrdersPortal sample data. No database was available while this page was written, so they were not run here; syntax and behavior were checked against the Microsoft Learn T-SQL reference and the expected results were worked out from that documented behavior.

Topic Use it when
String functions You build, cut, search or clean text.
Date and time functions You need “now”, date arithmetic, buckets or month ends.
Conversion functions You change a value’s data type or parse text safely.
User-defined functions You want reusable logic you can call inside a query.
Stored procedures You want a named, parameterized operation that applications execute.

The core string functions have been in T-SQL for decades: LEN, LEFT, RIGHT, SUBSTRING, CHARINDEX, REPLACE, UPPER, LOWER, LTRIM and RTRIM. CONCAT (SQL Server 2012) joins values and treats NULL as an empty string. SQL Server 2017 added TRIM, CONCAT_WS (concatenate with a separator, skipping NULLs), TRANSLATE and the STRING_AGG aggregate. SQL Server 2022 extended TRIM with LEADING/TRAILING and added an ordinal option to STRING_SPLIT. SQL Server 2025 adds regular expression functions such as REGEXP_LIKE and REGEXP_REPLACE, and the ANSI || concatenation operator.

Positions are 1-based: the first character is position 1. LEN counts characters but ignores trailing spaces, while DATALENGTH returns bytes (two per character for nvarchar in a UTF-16 collation).

SELECT Id,
CONCAT(N'#', Id, N' ', Name) AS Label,
LEFT(Name, CHARINDEX(N' ', Name) - 1) AS FirstName,
SUBSTRING(Email, CHARINDEX(N'@', Email) + 1, 320) AS EmailDomain
FROM dbo.Customers
WHERE Id IN (1, 3);
SELECT STRING_AGG(Name, N', ') WITHIN GROUP (ORDER BY Name) AS UsCustomers
FROM dbo.Customers
WHERE Country = 'US';
SELECT LEN(N'Ada ') AS LenResult, DATALENGTH(N'Ada ') AS Bytes, SUBSTRING('Hello', 0, 3) AS StartAtZero;

Expected result:

Id Label FirstName EmailDomain
1 #1 Ada Lopez Ada example.test
3 #3 Chen Wei Chen NULL
UsCustomers
Ben Ortiz, Chen Wei
LenResult Bytes StartAtZero
3 10 He

SUBSTRING('Hello', 0, 3) returns He, not Hel: when the start is below 1 the documented rule returns start + length - 1 characters from the beginning.

Pitfalls:

  • + versus CONCAT. N'Hi ' + Email is NULL for Chen Wei, CONCAT(N'Hi ', Email) is Hi . Decide which one you want.
  • CHARINDEX returns 0 when not found. LEFT(Name, CHARINDEX(N' ', Name) - 1) fails with “Invalid length parameter” for a one-word name. Guard it with NULLIF(CHARINDEX(...), 0).
  • Functions on a filtered column (WHERE UPPER(Email) = …) stop an index seek. With a case-insensitive collation the UPPER is unnecessary anyway.

Use the right type first: date for calendar days, datetime2(n) for timestamps and datetimeoffset when the offset matters (all SQL Server 2008). For the current moment, SYSDATETIME() and SYSUTCDATETIME() return datetime2(7); GETDATE() returns the older, less precise datetime. Store UTC and convert at the edge with AT TIME ZONE (SQL Server 2016).

The workhorses are DATEADD(part, n, date), DATEDIFF(part, start, end), DATEPART/DATENAME, EOMONTH (2012) and DATEFROMPARTS (2012). SQL Server 2022 added DATETRUNC (truncate to a month, hour and so on) and DATE_BUCKET (fixed-width buckets). DATEDIFF counts boundaries crossed, not elapsed time: DATEDIFF(year, '2026-12-31', '2027-01-01') is 1. Use DATEDIFF_BIG when the result can overflow int, for example milliseconds over long ranges.

DECLARE @AsOf date = '2026-11-18';
SELECT Id,
OrderDate,
DATEDIFF(day, OrderDate, @AsOf) AS AgeDays,
DATEADD(day, 30, OrderDate) AS PaymentDue,
DATETRUNC(month, OrderDate) AS OrderMonth, -- SQL Server 2022+
EOMONTH(OrderDate) AS MonthEnd
FROM dbo.Orders
WHERE Status = 'Shipped'
ORDER BY Id;

Expected result:

Id OrderDate AgeDays PaymentDue OrderMonth MonthEnd
1001 2026-10-02 47 2026-11-01 2026-10-01 2026-10-31
1002 2026-10-15 34 2026-11-14 2026-10-01 2026-10-31
1005 2026-11-03 15 2026-12-03 2026-11-01 2026-11-30

On SQL Server 2019 or earlier, replace DATETRUNC(month, OrderDate) with DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1).

Pitfalls:

  • Functions on the column in WHERE. WHERE YEAR(OrderDate) = 2026 cannot seek an index on OrderDate. Write the sargable range OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'.
  • Ambiguous literals. '03/11/2026' depends on the session’s DATEFORMAT and language. Use '2026-11-03' for date and datetime2, or '20261103' for every date type.
  • FORMAT for bulk work. FORMAT(OrderDate, 'yyyy-MM') is convenient but uses the .NET runtime per row and is far slower than CONVERT with a style; format in the application where you can.

CAST(expr AS type) is the ANSI form; CONVERT(type, expr [, style]) is the T-SQL form and adds style codes for dates and money, for example style 23 (yyyy-mm-dd) or 103 (dd/mm/yyyy). SQL Server 2012 added TRY_CAST, TRY_CONVERT and TRY_PARSE, which return NULL instead of failing when a value cannot be converted, and PARSE/TRY_PARSE for culture-aware parsing (they rely on the .NET runtime and are slower).

SQL Server also converts implicitly when types differ, following data type precedence: the lower-precedence side is converted. That is convenient and also the source of hidden performance problems.

SELECT TRY_CAST('12.5x' AS decimal(10,2)) AS BadNumber,
TRY_CONVERT(date, '2026-02-30') AS BadDate,
CONVERT(varchar(10), CAST('2026-11-03' AS date), 103) AS UkFormat,
CAST(19.99 AS decimal(10,2)) * 3 AS ExactTotal;

Expected result:

BadNumber BadDate UkFormat ExactTotal
NULL NULL 03/11/2026 59.97

Pitfalls:

  • Implicit conversion on the column side. Comparing a varchar column with an nvarchar parameter converts the column (higher precedence wins) and can turn an index seek into a scan; the plan shows a CONVERT_IMPLICIT warning. Match parameter types to column types.
  • TRY_CAST hides bad data. It is right for validation queries; in a load it can silently turn bad rows into NULLs. Count the NULLs it produced.
  • float for money. Approximate types cannot represent 19.99 exactly; use decimal(p,s) as above.

A user-defined function (UDF) is created with CREATE FUNCTION and comes in three kinds. A scalar function returns one value. An inline table-valued function (inline TVF) is a single RETURN (SELECT …) and behaves like a parameterized view: the optimizer expands it into the calling query. A multi-statement table-valued function fills a table variable in several statements and is opaque to the optimizer. Functions cannot change database state: no INSERT/UPDATE/DELETE on permanent tables and no TRY…CATCH.

Scalar UDFs used to run once per row and blocked parallelism. SQL Server 2019 introduced scalar UDF inlining, which rewrites eligible functions into the calling query under compatibility level 150 or later; check sys.sql_modules.is_inlineable. Prefer inline TVFs for reusable query logic, used with CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.CustomerOrderSummary (@CustomerId int)
RETURNS TABLE
AS
RETURN (
SELECT COUNT(*) AS OrderCount, COALESCE(SUM(Amount), 0) AS Total
FROM dbo.Orders
WHERE CustomerId = @CustomerId
AND Status <> 'Cancelled'
);
GO
SELECT c.Name, s.OrderCount, s.Total
FROM dbo.Customers AS c
CROSS APPLY dbo.CustomerOrderSummary(c.Id) AS s
ORDER BY c.Id;

Expected result:

Name OrderCount Total
Ada Lopez 2 200.50
Ben Ortiz 1 45.00
Chen Wei 0 0.00
Dana Fox 1 15.25

Pitfalls:

  • Multi-statement TVFs in joins. The optimizer uses a fixed row estimate (interleaved execution in SQL Server 2017+ helps in some cases), which leads to poor plans. Rewrite as inline when possible.
  • Scalar UDFs in WHERE. Even when inlined, a function around a column blocks a seek.
  • Side effects. Functions cannot call procedures that modify data; if you need to write, you need a stored procedure.

A stored procedure is a named batch of T-SQL created with CREATE PROCEDURE (or CREATE OR ALTER, SQL Server 2016 SP1) and run with EXEC. It can take input and OUTPUT parameters, return result sets and a return code, modify data, manage transactions and handle errors with TRY…CATCH. Applications call procedures to keep data access in one place, to grant EXECUTE permission instead of table permissions, and to reuse cached plans.

Start procedures with SET NOCOUNT ON to suppress the “rows affected” messages, and with SET XACT_ABORT ON when they change data, so a run-time error rolls back the whole transaction. Plans are compiled for the parameter values of the first call (parameter sniffing), which is usually good and occasionally the cause of “fast for one customer, slow for another”.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
@CustomerId int,
@OrderCount int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT Id, OrderDate, Status, Amount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC, Id DESC;
SET @OrderCount = @@ROWCOUNT;
END;
GO
DECLARE @Count int;
EXEC dbo.GetCustomerOrders @CustomerId = 2, @OrderCount = @Count OUTPUT;
SELECT @Count AS OrdersReturned;

Expected result:

Id OrderDate Status Amount
1004 2026-11-01 Cancelled 300.00
1003 2026-10-20 Pending 45.00
OrdersReturned
2

Pitfalls:

  • Dynamic SQL by concatenation. Building SQL strings from parameters invites SQL injection. Use sp_executesql with parameters, and QUOTENAME for object names.
  • The sp_ prefix. SQL Server looks for sp_ procedures in master first; use another prefix or none.
  • Swallowed errors. A CATCH block that does not roll back and re-raise with THROW hides failures from the caller. See Transactions.
Need Use
Reusable filter or calculation inside queries Inline table-valued function
A single computed value, simple logic Scalar UDF (check it is inlineable) or a computed column
An operation that changes data or controls transactions Stored procedure
Safe parsing of untrusted text TRY_CAST / TRY_CONVERT
Display formatting The application, or CONVERT with a style

Why does WHERE YEAR(OrderDate) = 2026 perform worse than a date range? Wrapping the column in a function makes the predicate non-sargable: SQL Server must compute the function for every row instead of seeking the index on OrderDate. A half-open range on the raw column lets it seek.

What is the difference between a stored procedure and a user-defined function? A function returns a value or table and can be used inside a query, but cannot change database state or use TRY…CATCH. A procedure is executed with EXEC, can modify data, manage transactions and return several result sets, but cannot be used inside a SELECT.

Why are inline table-valued functions preferred over multi-statement ones? An inline TVF is expanded into the calling query, so the optimizer sees the underlying tables, statistics and indexes. A multi-statement TVF fills a table variable that the optimizer treats with a fixed estimate, which often produces poor join choices.

What does DATEDIFF(month, '2026-01-31', '2026-02-01') return? 1, because DATEDIFF counts month boundaries crossed, not elapsed months. Use day differences or date arithmetic when you need elapsed time.

Senior scenario: a search procedure is fast for most customers but takes 30 seconds for one large customer after a restart. What is going on and what do you do? It is likely parameter sniffing: the cached plan was compiled for a small customer (a nested-loop seek plan) and is reused for the large one, or the other way round. Confirm with the actual plan’s compiled versus runtime parameter values and with Query Store. Options in order of preference: fix the indexing so one plan suits both; in SQL Server 2022+ rely on parameter-sensitive plan optimization; use OPTION (RECOMPILE) on that statement if it runs rarely; or force a known good plan with Query Store. Avoid blanket WITH RECOMPILE on busy procedures.