What restrictions exist on what a user defined function is allowed to do compared to a stored procedure?

Intermediate

Answer

A user defined function generally cannot modify data in permanent tables, cannot call stored procedures, cannot use certain non deterministic functions like GETDATE in specific contexts within some function types, and must always return a value, all of which are restrictions designed to keep functions predictable and safely usable directly within other queries like SELECT statements.

-- This is not allowed inside a function
CREATE FUNCTION dbo.BadFunction()
RETURNS INT
AS BEGIN
  UPDATE Orders SET Status = 'Processed'; -- not permitted
  RETURN 1;
END;

Real-world example A developer initially tries to update a table from within a function, learns this is not permitted, and instead moves that logic into a stored procedure while keeping the function focused purely on calculation and returning a value.

Common follow-ups: Why are these restrictions important for how functions can be safely used within queries?;What happens if you try to violate one of these restrictions?

Stored Procedures & Functions;Data Types & Schema Design

All 7 User Defined Functions questions