Introduction
A stored procedure in SQL Server is a collection of SQL queries that is used to execute from the applications to retrieve, insert, update, and delete data. Stored procedures run on the server, hence puts less load on the client side. Stored procedures have many benefits over inline SQL queries.
A function or user-defined function in SQL Server is a user defined object, collection of SQL and code that is executed on the server. Functions in SQL can return values and take input parameters.
Both, stored procedures and functions are used to execute large SQL queries but there may be time, when you need to choose between a function and a stored procedure. This article explains the difference between a function and a stored procedure. The article also explains when to use a function vs stored procedure.
Differences between stored procedures and user defined functions in RDBMS.
Difference 1. Stored procedure is used to execute a query but a function is used for computing values
A stored procedure is used to perform specific tasks.
A stored procedure is normally used to perform a speck task. The bulk of SQL statements are compiled and use the cached execution plans. It can return more than one result set.
A function is used for computing values. Functions are used to do the calculations instead of executing a query. A function can be called from multiple places where the value is needed.
Difference 2. A stored procedure may not return values but a function always return a value
Stored procedures may or may not return values.
The stored procedure based on query type it will do the operation. If we write any select query then it will return the results. If we do only update, insert or delete then it won't return any results. However, if you want to check the confirmation of the transaction then we can return the result. It is not compulsory to return the result set.
But the function should return a value,
The function must return a value. Based on the function type it will return the results.
A scalar function returns a single value. A table-valued function returns multiple rows. We cannot write the function without returning any value to the calling program.
Difference 3. A stored procedure cannot be called in SELECT but a function can
A stored procedure cannot be used in the select/where/having clause
The stored procedure cannot be called like the following.
SELECT * FROM Pr_RetrieveEmployees -- It will throws an error
It will throw an error. Similarly, the stored procedure cannot be part of the SQL query anywhere.
But the function can be called from select/where/having clause
The function can be called using the select query.
It can be called from the select/where/having clause.
For instance SELECT [dbo].fn_EmployeeSalary (5) Ã it is scalar UDF. It returns a single value.
SELECT * FROM fn_EmployeeHistory (3) Ã it will return multi-value.
Difference 4. Stored procedures are executed independently but functions cannot.
Stored procedures can run independently. It can be executed using EXECUTE or EXEC command
The stored procedure can run independently. Once the stored procedure is compiled then it can be executed. It can be executed using the SQL command statement EXECUTE or EXEC.
EXECUTE proc_RetrieveEmployeeDetails EXEC proc_RetrieveEmployeeDetails proc_RetrieveEmployeeDetails
But the function cannot run independently
The function cannot run independently. It has to be part of the SQL statement.
Difference 5. A functions can't create a temp table.
The temporary table cannot be created in a function. As you know, if you create a temp table then it will be stored on the tempdb database. But the temp table won't allow us to create inside the function.
There are two ways to create the temp table.
- Create temp table
- Derived table
SELECT * INTO #tmpEmployee FROM Employees
The above statement is a derived table. It cannot create in a function.
But it can be created using a stored procedure. A stored procedure allows us to create temp tables.
Difference 6. Functions don't support try..catch but stored procedures do.
From SQL server 2005 onwards, TRY CATCH statements can be used in the stored procedures.
The TRY CATCH is one of the new features in the SQL server 2005 edition. It can be used inside the stored procedure. As you know it handles the error in the catch block, whatever the statements are written in the try block.
But it cannot be used in the function. But we can use the raise error function.
The TRY CATCH block cannot be used inside the functions. But we can use the raiserror function to throw the exception.
Difference 7. A strored prodedure can call functions but a function cannot call a stored procedure.
A stored procedure can call user-defined functions.
A function can be called from a stored procedure.
CREATE PROC Pr_RetirveCustomers AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON SELECT * FROM Customers SELECT *
FROM [dbo].fn_GetOrderedCustomers (5) END
But a function cannot call stored procedures.
The function cannot call stored procedures like procedures. There are many types of stored procedures in SQL servers.
Except extended stored procedures, other types of stored procs cannot call user-defined functions.
Difference 8. Stored procedures support both input and output parameters but functions can have input parameters only.
Stored procedures can have input and output parameters.
As you know, the input and output are the parameters and can also return results through parameter variables. The output parameter can be only used to return the results through the output variable. But the input parameter can be do both input and output operations.
But the function can have input parameters only. No output parameters allowed in functions.
Difference 9. Functions are limited to few DML operations.
Stored procedures can have select and all DML operations.
The stored procedures can do all the DML operations like inserting the new record, updating the records, and deleting the existing records.
But functions can execute select operations only. Functions won't allow us to use DML operations on database tables similar to what stored procedures do. But we can use DML operations on table variables inside user-defined functions.
Difference 10. Stored procedures support transaction statements but functions don't.
A function cannot have transaction statements.
Transaction statements are allowed in stored procedures.
Difference 11. Stored procedures can use all the data types available in the SQL server but functions are limited.
Parameters used in stored procedures can be any data type that is available on the SQL server. Functions don't support text, image, and timestamp data types as return types.
Difference 12. Stored procedures support limited use of table variables.
Stored procedures can create table variables and cannot return a table variable.
A table variable is one of the performance tuning mechanisms. Because it takes minimum resources and it uses the memory location for store the data. (Recommended for minimum rows)
It can be created and do the operations. But it cannot be the return type.
A function can create, update and delete table variables. It can also return table variables. It can be created and can do all the DML operations and it can be the return type. That is called the multi-valued table function.
Difference 13. Functions cannot execute sp_executesql statement.
A stored procedure can have a dynamic SQL statement and which can be executed using the sp_executesql statement.
The stored procedure can have the dynamic SQL statement for the complex decision-making operations which generated inside the stored procedures. It can be executed using the sp_executesql statement.
But the function cannot execute the sp_executesql statement.
The function can generate the dynamic SQL statement. But it cannot get executed. It will not allow writing the sp_executesql command to execute the dynamically created SQL statement.
Difference 14. Functions don't allow non-deterministic functions but stored procedures do.
Stored procedures allows getdate () or other non-deterministic functions.
The stored procedure will allow all the SQL server built-in functions like getdate(), DB_ID(),
DB_NAME (), etc..,
Functions don't allow non-deterministic functions.
The function will not allow using non-deterministic functions like GETDATE ().
Conclusion
If you are a DBA, software or data developer, its important to understand the differece between stored procedures and functions in SQL Server. In this article, we learned about common and major differences between a stored procedure and a function in SQL Server.
Here is another article on the same topic - Difference between Stored Procedure and User-Defined Function in SQL Server.
If you want to learn more about stored procedures and functions in SQL Server, here are two tutorials:
If you find any mistakes or have any suggestions, please post it here.