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.
Arun GPosted Jan 18, 2018, 10:10 AM
Thanks for giving a valuable points..
Manav PandyaPosted Jan 9, 2017, 11:22 PM
Thanks sir , nice pros and cons you have collected
Mahesh DharmarajPosted Dec 20, 2012, 6:02 AM
Thanks for the Valuable post... :)
prabhu gengaramPosted Dec 3, 2012, 12:25 AM
thanks,good article
Sreekanth vPosted Oct 13, 2012, 11:44 PM
good article.
janarthanPosted Aug 23, 2012, 9:56 AM
Thanks!Good article.
che kePosted Jul 27, 2012, 12:46 AM
Good article.
che kePosted Jul 27, 2012, 12:46 AM
Good article.
Eamonn GallagherPosted Jul 14, 2012, 10:05 AM
Thanks for putting together such an informative article
Shadab ShahPosted Jul 8, 2012, 11:10 PM
I think there is some contradiction in points 8 and 11. In point 8th Senthilkumar mention that "But the function can have only INPUT PARAMETER." where as in point 11th he mention that "But the function cannot use the ntext, image and timestamp data types as RETURN TYPE". Now my question is if there cannot be any output parameter then how can the function return the value.
Akiii LethalPosted Jun 8, 2012, 11:28 PM
excellent article !
pandeharsheditedPosted May 4, 2012, 5:13 AMEdited May 4, 2012, 5:19 AM
"The function will not allow using non-deterministic functions like GETDATE ()"Is this correct ? Please Explain Now getdate() is allowed inside function, sql server 2005 onwards
ravi kumarPosted Apr 20, 2012, 12:12 PM
Best Article , Thank you very much Senthilkumar
bhawnaPosted Jan 27, 2012, 12:05 PM
Thanks for posting this article.That helps so much
Vilas GitePosted Aug 8, 2011, 5:44 AM
Hi Erode... Its really good article and nice analysis. :)
hamida khanamPosted Mar 15, 2011, 12:13 PM
Many thanks to Erode SenthiKumar for explaining in simple and nice way.I had so many confusions on this topic but thanks to Shrikant S who suggest me to read this article.This topic is awesome and best in itself.You had explained the difference in quite easiey manner so it it is easy for others to understand.Thanks for helping students like me.it is really fruitful topic.Hats off.......
deivanai palaniappanPosted Mar 9, 2011, 4:31 PM
Really Nice. Wonderful Senthil
vivek kPosted Feb 7, 2011, 12:00 AM
IN THE DIFFERENCE 12 YOU MENTIONED LIKE IN FUNCTIONS WE CAL DO DML OPERATIONS HOW ITS POSSIBLE?
vivek keditedPosted Feb 6, 2011, 11:53 PMEdited Feb 7, 2011, 12:01 AM
hi SENTHIL U know the exact work flow of data in correlated sub query ..if so please post that with one example..
Rashmi Ranjan FatesinghPosted Jan 29, 2011, 10:43 AM
Thanks senthilkumar for your valuable articles.. in difference you have described that -- The temporary table cannot be created in the function. but i think it can be done.. in case , when u want to check first character of all the words in a string,then u can return a temporary table having a column , which can hold all the first letters.
Rashmi Ranjan FatesingheditedPosted Jan 29, 2011, 10:37 AMEdited Jan 29, 2011, 10:44 AM
Thanks senthilkumar for your valuable article.
satya murthyPosted Jan 20, 2011, 7:09 AM
Hi ..... Thanq ... Nice Article Really Good ... I didnt Find This Much information ..anywhere. :)
Meera KhannaPosted Dec 22, 2010, 4:35 AM
Very nice document ... Really appreciable ... I am proud we belong to same state :O)
Guna sundariPosted Nov 24, 2010, 1:06 AM
Hi sir, It really helpful to me..I am a beginner of .net..and i need to know this concept(stored procedures and functions) via sample example..
Chandra Sekhar KPosted Oct 7, 2010, 6:23 AM
Thanks a lot for sharing ur knowledge.
Zinnia SarkarPosted Oct 4, 2010, 11:51 PM
Really it is helpful!
uthara rPosted Sep 28, 2010, 8:07 AM
Thank You for this article. It really helps me
sreenivas santhapalliPosted Jun 27, 2010, 10:52 AM
thank bass
Gaurish ShuklaPosted Jun 10, 2010, 6:01 PM
Thank You So Much Senthil. Really nice and helpful article. Please keep doing this kind of Good job. Gaurish Shukla