Stored Procedure vs User Define Function in SQL

Stored Procedures vs. User Defined Functions

Stored Procedure:

A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database's command environment.

Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data.

Create Procedure GetSalesById
(
@Id int,
@Sales money OUTPUT
)
As
SELECT @Sales = sales
FROM sample
WHERE id = @Id

Running the stored procedure:

GetSalesById.
Passing it two parameters:
@Id = 1
@Sales = (an output parameter)
It returned the value: 5000.

CREATE PROCEDURE usp_adduser
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35) //datatype and size should match the table definition
AS
INSERT INTO USERLIST (login, pswd, f_name, l_name)
VALUES (@login, @pswd, @f_name, @l_name)

To Execute:

exec usp_adduser 'dnelson', 'dean2003', 'Dean'

User-Defined Functions:

Microsoft has introduced the concept of User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
There are three types of User-Defined functions in SQL Server 2000 and they are

1. Scalar,
2. Inline Table-Valued and
3. Multi-statement Table-valued.

Scalar User-Defined Function

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.

Below is an example that is based in the data found in the NorthWind Customers Table.

CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end

return @return
end

Execute:

print dbo.WhichContinent('Argentina')


Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition.

Inline Table-Value User-Defined Function

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Benefits of User-Defined Functions

The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.

Difference:

SQL Server user-defined functions and stored procedure offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by:

Reusing code from one program to another, cutting down on program development time
Hiding the SQL details, allowing database developers to worry about SQL and application developers to deal only in higher-level languages Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications
At first glance, functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two:

Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.

Procedure can return zero or n values whereas function can return one value which is mandatory.

Procedures can have input, output parameters for it whereas functions can have only input parameters.

Procedure allows select as well as DML statement in it whereas function allow only select statement in it.

Functions can be called from procedure whereas procedures cannot be called from function.

Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

We can go for transaction management in procedure whereas we can't go in function.

Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.