What is the difference between a function and a stored procedure in SQL Server?

In Microsoft SQL Server, both functions and stored procedures are reusable database objects, but they are designed for different purposes.

Difference Between Function and Stored Procedure

Feature	Function	Stored Procedure
Purpose	Returns a value or table	Performs actions/operations
Return Type	Must return a value	May or may not return values
Calling Method	Can be used inside SQL statements	Executed using `EXEC` or `EXECUTE`
Use in SELECT	Yes	No
Data Modification	Limited or restricted	Fully allowed (`INSERT`, `UPDATE`, `DELETE`)
Transactions	Cannot manage transactions	Can use transactions
Error Handling	Limited	Supports `TRY...CATCH`
Parameters	Input parameters only	Input and output parameters
Multiple Result Sets	Not allowed	Allowed
Side Effects	Should not change DB state	Can change DB state

Example of a Function

CREATE FUNCTION AddNumbers
(
    @a INT,
    @b INT
)
RETURNS INT
AS
BEGIN
    RETURN @a + @b
END;

Usage:

SELECT dbo.AddNumbers(5, 3);

The function behaves like a built-in SQL function.

Example of a Stored Procedure

CREATE PROCEDURE AddEmployee
(
    @Name VARCHAR(50),
    @Salary INT
)
AS
BEGIN
    INSERT INTO Employees(Name, Salary)
    VALUES(@Name, @Salary)
END;

Usage:

EXEC AddEmployee 'John', 50000;

This procedure performs an action on the database.

Key Practical Difference

Use a function when you need a computed value that can be used inside queries.
Use a stored procedure when you need to perform tasks, modify data, handle transactions, or execute complex business logic.

Simple Analogy

Function → Like a calculator formula that returns a result.
Stored Procedure → Like a workflow that performs a complete task.