---
title: "What is the difference between a function and a stored procedure in SQL Server?"  
description: "What is the difference between a function and a stored procedure in SQL Server?"  
author: "Anubhav Sharma"  
published: 2026-05-14  
updated: 2026-05-18  
canonical: https://answers.mindstick.com/qa/116610/what-is-the-difference-between-a-function-and-a-stored-procedure-in-sql-server  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 2 minutes  

---

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

## Answers

### Answer by Ravi Vishwakarma

In Microsoft SQL Server, both **functions** and **[stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application)** are reusable [database objects](https://answers.mindstick.com/qa/30925/what-are-the-various-oracle-database-objects), but they are designed for different purposes.

## Difference Between Function and Stored Procedure

| Feature | Function | [Stored Procedure](https://www.mindstick.com/articles/202/the-detailed-concept-of-stored-procedure-in-sql-server) |
| --- | --- | --- |
| 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](https://www.mindstick.com/articles/1825/objective-c-error-handling) | Limited | Supports `TRY...CATCH` |
| Parameters | Input parameters only | [Input and output](https://www.mindstick.com/forum/161309/what-are-the-features-of-the-console-class-for-input-and-output-in-java) parameters |
| Multiple Result Sets | Not allowed | Allowed |
| Side Effects | Should not change DB state | Can change DB state |

## Example of a Function

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

Usage:

```plaintext
SELECT dbo.AddNumbers(5, 3);
```

The function behaves like a built-in SQL function.

## Example of a Stored Procedure

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

Usage:

```plaintext
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](https://www.mindstick.com/interview/34242/how-does-entity-framework-handle-transactions), or execute [complex business](https://www.mindstick.com/forum/161972/is-sql-more-vital-for-solving-complex-business-problems-or-power-bi-tableau-for-insights) logic.

## Simple Analogy

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


---

Original Source: https://answers.mindstick.com/qa/116610/what-is-the-difference-between-a-function-and-a-stored-procedure-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.