---
title: "Differences between stored procedures and functions in SQL Server"  
description: "Choosing between a stored procedure and a function depends on whether you need to perform data manipulation and complex logic or calculate and return"  
author: "Ashutosh Patel"  
published: 2024-07-08  
updated: 2024-07-08  
canonical: https://www.mindstick.com/articles/336349/differences-between-stored-procedures-and-functions-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 4 minutes  

---

# Differences between stored procedures and functions in SQL Server

#### Difference Between SQL Stored Procedures and Functions

Stored [procedures and functions](https://www.mindstick.com/interview/2542/how-can-we-execute-stored-procedures-and-functions) serve different purposes and have many differences in terms of their usage, capabilities, and execution.

#### SQL Stored Procedures

## purpose

- [Stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application) are specifically designed to perform one or more tasks or operations in the database.
- They can create complex SQL queries, procedural logic (conditional loops), and other programming constructs.
- It is commonly used to perform data manipulation (`INSERT`, `UPDATE`, `DELETE`), data retrieval, and business logic applications.

\
**Usage**

- Stored procedures can be called independently or from SQL statements or other programs.
- You can accept input parameters and [return multiple](https://www.mindstick.com/forum/55125/how-to-create-generic-method-to-return-multiple-list-in-c-sharp) result sets.

\
**Transactions**

- Transactions can be started and executed, allowing multiple SQL functions to be compiled and executed atomically.

\
**Security**

- They provide better authorization control because they can grant direct access to the stored method to control access to the underlying data.

\
**Execution**

- The `EXECUTE` or `EXEC` command is used to execute a Stored [Procedure in SQL](https://www.mindstick.com/forum/156783/why-an-int-datatype-variable-accept-a-string-format-value-in-stored-procedure-in-sql-server) Server.

## Syntax-

The SQL `CREATE PROCEDURE/PROC` statement is used to create a new [Stored Procedure](https://www.mindstick.com/forum/12886/stored-procedure-error-transaction-count-mismatch) in SQL Server, and `ALTER PROCEDURE/PROC` statement is used to Alter or Update the existing procedures,

```javascript
USE DatabaseName
GO
CREATE/ALTER PROCEDURE Proc_ProcedureName
(
-- Parameter name if need
)AS
BEGIN
-- SQL statement for perform in stored procedure
END
```

## Example-

Here is a stored procedure that returns the data of a user from the `Users` table by user ID,

```javascript
USE MyCollegeDb
GO
CREATE PROCEDURE Proc_ProcedureName
(
@UserID BIGINT
)AS
BEGIN
SET NOCOUNT ON;
 -- SQL statement that returns the user details based on parameter value
 SELECT * FROM Users WHERE UserID = @UserID
SET NOCOUNT OFF;
END
```

Now, click on **Execute** the above SQL statement from the SSMS Header or press **F5** from the keyboard to create the Stored Procedure,

Let's execute the created stored procedure,

![Differences between stored procedures and functions in SQL Server](https://www.mindstick.com/mindstickarticle/7c5e1e50-c56f-43ca-a9ad-4616abfe257e/images/4a90c9c1-a762-487e-94c2-8b9d7a4706f5.png)

#### SQL Functions

## purpose

- The functions are designed to return a single value or a table.
- It is used to contain reusable logic that calculates and returns scalar values ​​or table results based on input parameters.

## Usage

- Functions are often used in `SELECT` statements, `WHERE` clauses, `JOIN` conditions, and other areas where expressions are allowed.

## Return Types

- A value must be returned and **DML** operations (`INSERT`, `UPDATE`, `DELETE`) cannot be performed directly on the database.

## Transactions

- Functions do not support explicit [transaction control](https://www.mindstick.com/forum/34154/how-many-types-of-transaction-control-in-sql) like stored procedures.

**Security**\
Permissions are derived from referenced objects, and permissions should be granted to underlying objects (`tables`, `views`) rather than directly to the user

**Execution**\
Scalar [functions in SQL](https://www.mindstick.com/forum/55083/what-is-the-role-of-functions-in-sql-server) statements are often called inline, while table-valued functions can be used like [tables in SQL](https://www.mindstick.com/forum/156819/what-is-local-and-global-temporary-tables-in-sql-server) queries.

## Example-

The below SQL Function takes two argument values **BirthDate** and **UserID** based on the Users table and returns the user Age,

```javascript
USE MyCollegeDb
GO
CREATE FUNCTION CalculateUserAge(@BirthDate DATE, @UserID INT)
RETURNS INT
AS
BEGIN
DECLARE @Age INT;
   SET @Age = (SELECT DATEDIFF(YEAR, @BirthDate, GETDATE()) AS Age FROM Users WHERE UserID = @UserID);
RETURN @Age;
END;
```

Execute the SQL statement above to create a function.

Let's execute the function above,

![Differences between stored procedures and functions in SQL Server](https://www.mindstick.com/mindstickarticle/7c5e1e50-c56f-43ca-a9ad-4616abfe257e/images/80195883-626a-4d9c-944f-3a89e88f79d1.png)

Choosing a stored method and a function depends on whether you need to perform data transformations and complex logic (using stored procedures) or calculation of return values ​​(using functions). Typically, both are used together to access application logic components in a SQL [Server database](https://www.mindstick.com/forum/155643/how-to-create-sql-server-database-in-google-cloud).

**Also, Read:** [Explain the SQL triggers and their uses](https://www.mindstick.com/articles/336344/explain-the-sql-triggers-and-their-uses)

---

Original Source: https://www.mindstick.com/articles/336349/differences-between-stored-procedures-and-functions-in-sql-server

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