What are the Differences Between SQL Server Stored Procedures and Functions?

Senthilkumar

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.

Stored Procedure vs Functionfunction vs procedure

33 Comments

Join the conversation! Your thoughts help the community grow.

Arun G8y ago
Thanks for giving a valuable points..
Manav Pandya9y ago
Thanks sir , nice pros and cons you have collected
Mahesh Dharmaraj13y ago
Thanks for the Valuable post... :)
prabhu gengaram13y ago
thanks,good article
Sreekanth v14y ago
good article.
janarthan14y ago
Thanks!Good article.
che ke14y ago
Good article.
che ke14y ago
Good article.
Eamonn Gallagher14y ago
Thanks for putting together such an informative article
Shadab Shah14y ago
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.
- test test12y ago
  we need not have output parameter to return value
- test test12y ago
  Just we need to return variable which is declared inside function scope
Akiii Lethal14y ago
excellent article !
pandeharsh14y agoedited
"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 kumar14y ago
Best Article , Thank you very much Senthilkumar
bhawna14y ago
Thanks for posting this article.That helps so much
Vilas Gite15y ago
Hi Erode... Its really good article and nice analysis. :)
- Senthilkumar14y ago
  Thank you guys!!! I know this will be very useful... I have written based on my experience and other articles read... Once again thanks for your appreciation and support...
hamida khanam15y ago
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 palaniappan15y ago
Really Nice. Wonderful Senthil
vivek k15y ago
IN THE DIFFERENCE 12 YOU MENTIONED LIKE IN FUNCTIONS WE CAL DO DML OPERATIONS HOW ITS POSSIBLE?
- Senthilkumar15y ago
  Hi Vivek, I have mentioned like you can do DML operation in the function using table variable... But normally you can't do DML with table.
vivek k15y agoedited
hi SENTHIL U know the exact work flow of data in correlated sub query ..if so please post that with one example..
Rashmi Ranjan Fatesingh15y ago
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 Fatesingh15y agoedited
Thanks senthilkumar for your valuable article.
satya murthy15y ago
Hi ..... Thanq ... Nice Article Really Good ... I didnt Find This Much information ..anywhere. :)
Meera Khanna15y ago
Very nice document ... Really appreciable ... I am proud we belong to same state :O)
Guna sundari16y ago
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 K16y ago
Thanks a lot for sharing ur knowledge.
Zinnia Sarkar16y ago
Really it is helpful!
uthara r16y ago
Thank You for this article. It really helps me
sreenivas santhapalli16y ago
thank bass
Gaurish Shukla16y ago
Thank You So Much Senthil. Really nice and helpful article. Please keep doing this kind of Good job. Gaurish Shukla

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.

System Stored procedure
User-defined Stored procedure
NET CLR stored procedure
Extended stored procedure

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.