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.
Topics on this page
Section titled “Topics on this page”| 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. |
String functions
Section titled “String functions”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 EmailDomainFROM dbo.CustomersWHERE Id IN (1, 3);
SELECT STRING_AGG(Name, N', ') WITHIN GROUP (ORDER BY Name) AS UsCustomersFROM dbo.CustomersWHERE Country = 'US';
SELECT LEN(N'Ada ') AS LenResult, DATALENGTH(N'Ada ') AS Bytes, SUBSTRING('Hello', 0, 3) AS StartAtZero;Expected result:
Id Label FirstName EmailDomain1 #1 Ada Lopez Ada example.test3 #3 Chen Wei Chen NULL
UsCustomersBen Ortiz, Chen Wei
LenResult Bytes StartAtZero3 10 HeSUBSTRING('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:
+versusCONCAT.N'Hi ' + EmailisNULLfor Chen Wei,CONCAT(N'Hi ', Email)isHi. Decide which one you want.CHARINDEXreturns 0 when not found.LEFT(Name, CHARINDEX(N' ', Name) - 1)fails with “Invalid length parameter” for a one-word name. Guard it withNULLIF(CHARINDEX(...), 0).- Functions on a filtered column (
WHERE UPPER(Email) = …) stop an index seek. With a case-insensitive collation theUPPERis unnecessary anyway.
Date and time functions
Section titled “Date and time functions”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 MonthEndFROM dbo.OrdersWHERE Status = 'Shipped'ORDER BY Id;Expected result:
Id OrderDate AgeDays PaymentDue OrderMonth MonthEnd1001 2026-10-02 47 2026-11-01 2026-10-01 2026-10-311002 2026-10-15 34 2026-11-14 2026-10-01 2026-10-311005 2026-11-03 15 2026-12-03 2026-11-01 2026-11-30On 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) = 2026cannot seek an index onOrderDate. Write the sargable rangeOrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'. - Ambiguous literals.
'03/11/2026'depends on the session’sDATEFORMATand language. Use'2026-11-03'fordateanddatetime2, or'20261103'for every date type. FORMATfor bulk work.FORMAT(OrderDate, 'yyyy-MM')is convenient but uses the .NET runtime per row and is far slower thanCONVERTwith a style; format in the application where you can.
Conversion functions
Section titled “Conversion functions”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 ExactTotalNULL NULL 03/11/2026 59.97Pitfalls:
- Implicit conversion on the column side. Comparing a
varcharcolumn with annvarcharparameter converts the column (higher precedence wins) and can turn an index seek into a scan; the plan shows aCONVERT_IMPLICITwarning. Match parameter types to column types. TRY_CASThides bad data. It is right for validation queries; in a load it can silently turn bad rows intoNULLs. Count theNULLs it produced.floatfor money. Approximate types cannot represent 19.99 exactly; usedecimal(p,s)as above.
User-defined functions
Section titled “User-defined functions”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 TABLEASRETURN ( 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.TotalFROM dbo.Customers AS cCROSS APPLY dbo.CustomerOrderSummary(c.Id) AS sORDER BY c.Id;Expected result:
Name OrderCount TotalAda Lopez 2 200.50Ben Ortiz 1 45.00Chen Wei 0 0.00Dana Fox 1 15.25Pitfalls:
- 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.
Stored procedures
Section titled “Stored procedures”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 OUTPUTASBEGIN 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 Amount1004 2026-11-01 Cancelled 300.001003 2026-10-20 Pending 45.00
OrdersReturned2Pitfalls:
- Dynamic SQL by concatenation. Building SQL strings from parameters invites SQL injection. Use
sp_executesqlwith parameters, andQUOTENAMEfor object names. - The
sp_prefix. SQL Server looks forsp_procedures inmasterfirst; use another prefix or none. - Swallowed errors. A
CATCHblock that does not roll back and re-raise withTHROWhides failures from the caller. See Transactions.
Choosing between them
Section titled “Choosing between them”| 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 |
Interview questions
Section titled “Interview questions”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.
Related pages
Section titled “Related pages”- Querying data: aggregates and the sample data.
- Joins, subqueries and CTEs:
CROSS APPLYand derived tables. - Changing data: transactions and error handling used inside procedures.
- Tables, constraints and indexes: data types and computed columns.