If you have just started learning SQL, a question may arise in your mind "What is the difference between Stored procedure and function in SQL Server?", so today in this article, I will quickly list out important differences between functions and stored procedures in SQL server.
Stored Procedure in SQL server
A stored procedure in SQL Server is a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method. Procedures resemble constructs in other programming languages because they can:
Accept input parameters and return multiple values in the form of output parameters to the calling program.
Contain programming statements that perform operations in the database. These include calling other procedures.
Return a status value to a calling program to indicate success or failure (and the reason for failure).
For this example, I will be using AdventureWorks2012 SQL server sample database, if you don't have AdventureWorks Database, you can download and restore AdventureWork database first
Now, let's create a stored procedure, which has input paramters as LastName,FirstName and gives output as FirstName, LastName, Department based on user first and last name from HumanResources.vEmployeeDepartmentHistory table
USE AdventureWorks2012; GO CREATE PROCEDURE HumanResources.uspGetEmployeesTest2 @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL; GO
You can execute the above stored procedure, using the new query as below
EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar'; GO
Benefits of Stored procedures:
- Reduces server/client network traffic as the commands in a procedure are executed as a single batch of code.
- Provides stronger security.
- Easier to maintain, as when client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.
- Provides better performance as a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.
Functions (User-defined functions) in SQL server
A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. In other words, Function may contain a set of statement as stored procedure but generally we create function if there is some calculations which we can do frequently.
We will again use the same database AdventureWorks2012, so let's create a function in it as below, it returns an inline table-valued function in the AdventureWorks2012 database. It returns three columns
Name and the aggregate of year-to-date totals by store as
YTD Total for each product sold to the store.
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE AS RETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name ); GO
Now, you can invoke the function, run this query:
SELECT * FROM Sales.ufn_SalesByStore (602);
Difference between Stored procedure and Functions
Here is the list of differences:
- Stored procedure may or may not return a value but if it returns a value it must be of int data type,but function return the values of any data type.
- It is mandatory for Function to return a value while it is not for stored procedure.
- A stored procedure can return multiple parameters but a function can return only one value.
- We can't use Stored procedures in SELECT/WHERE/HAVING but We can use user-defined functions in SELECT/WHERE/HAVING statement.
- Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function
- We can use the function within the Queries, but for stroed procedure's we won't able to use it in the queries.
- Transaction management is not possible in functions but it is possible in Stored procedures.
- We can implicitely execute the stored procedure's. But function's we can't.
- Print function can not be called within the function but it can be called within the stored procedure.
- Exception handling can be done in Stored procedure but not in function.
- Stored procedure will always allow for return to zero. User defined functions, on the contrary, has values that must come-back to a predetermined point.
- Stored Procedure can use temporary tables While functions cannot.
Difference in Tabular form
|STORE PROCEDURE||FUNCTION (USER DEFINED FUNCTION)|
|Procedure can return 0, single or multiple values||Function can return only single value|
|Procedure can have input, output parameters||Function can have only input parameters|
|Procedure cannot be called from a function||Functions can be called from procedure|
|Procedure allows select as well as DML statement in it||Function allows only select statement in it|
|Exception can be handled by try-catch block in a procedure||Try-catch block cannot be used in a function|
|We can go for transaction management in procedure||We can not go for transaction management in function|
|Stored Procedure cannot be utilized in a select statement||Function can be embedded in a select statement|
|Stored Procedure can affect the state of database means it can perform CRUD operation on database||Function can not affect the state of database means it can not perform CRUD operation on database|
|Stored Procedure can use temporary tables||Function can not use temporary tables|
|Stored Procedure can alter the server environment parameters||Function can not alter the environment parameters|
|Stored Procedure can use when we want instead is to group a possibly- complex set of SQL statements||Function can use when we want to compute and return a value for use in other SQL statements|
I hope above details clears all the difference betweem SP and Function.
You may also like to read: