Comparing Functions and Stored Procedures in SQL Server

Overview

Functions are routine that perform actions like complex calculations, accept input parameter and return the result of that action as a value, whereas, Stored Procedure are prepared SQL code that can be used over and over again.

However, the function that we used in SQL Server is little different from the one we used in programming. SQL Server Function can have only input parameter, but Stored Procedure can have input/output parameter.

Let us understand it in a detailed manner the difference between stored procedure and function easily.First I will explain about the functions and then about Stored Procedure.

Table of Content

• What are Functions
• What are Stored Procedures
• Types of Stored procedures
• Write A Stored Procedure in SQL
• Function Vs. Stored Procedure
• Conclusion
• FAQs

What are Functions in SQL Server

• Scalar functions in SQL Server will return only a single value wheras table-valued functions will return a table.
• A deterministic function will return the same value when particular sets of input values are given,for example, addition of two numbers whereas non-deterministic function will return different result when called with specific set of input values for example, function AVG.
• Functions cannot make any permanent alterations to SQL Server (i.e. no INSERT or UPDATE operations are allowed).
• If function returns a scalar value, it can be used inline in SQL statements.

Types of Functions in SQL Server

1. System Defined Function
2. User Defined Function

(a) System Defined Function:

These functions are already defined in the system. It is subdivided into two types. Before we move toward the stored procedure vs function, let’s discuss these two types of functions.

i. System Scalar Function

These functions operate on a single value and give it as a result. Also, Some System Scalar functions are:

• round()- This function will round off the number up to 3 places. For example,round(28.64851) will give 28.649
• upper()- It will return the upper case of the given string. For example, upper(‘english’) will give ENGLISH and lower (‘ENGLISH’) as english
• rand() – This function will return a random number or a random number within range. For example, rand(8) will give any randomnly generated value, 0.71372242401

ii. System Aggregate Function

These functions operate with set of input parameters and return a single value. Some examples are:

• Avg()- It will give average value of all the set of inputs given
• Count()- It will return the number of rows that matches a specified criteria.
• Max() and min() – It will give maximum and minimum among the set of input parameters.

(b) User Defined Function:

It contains functions that are defined by the users in the system database. It is divided into three types:

i. Scalar Function

User defined scalar functions gives a single value from the step of actions performed by function. It returns any data type value from function.

E.g. If we have created a table “Student” and have put entries like Student ID, FirstName, LastName, Class, Location and we want only the full names of the students to be shown then, we can create function “GetFullName”

Creating Function

Create function fnGetFullName
(
@FirstNamevarchar(30),
@LastNamevarchar(30)
)
Returns varchar(65)
As
Begin return (Select @FirstName + ‘ ’ + @LastName);
      end

Calling the created function

Selectdbo.fnGetFullName(FirstName,LastName) as Name, Class from Student

ii. Inline Table-Valued Function

User defined inline table-valued Function gives the result as a table , performed by the function. It has no BEGIN/END body.Only a single SELECT statement is used to derive the result. Evidently, users must understand these points carefully to know Stored Procedure vs Function in depth.

Creating Function
Create function fnGetStudent()
Returns Table
As
return (Select * from Student)

Calling the function
Select * from fnGetStudent()

iii. Multi-Statement Table-valued Function

If the user defined Function consists of more than one SELECT statement or contains a SELECT statement that cannot be updated, then the result returned by that function is not updated. We need to define the value which can be derived from different sql statements and there is need to declare table variable explicitly.

Now we’re going to discuss stored procedures in SQL through this guide for understanding the difference between Stored procedures and Functions of SQL Server.

Also Read: Copy Stored Procedure From One Server to Another Smartly

What are Stored Procedures in SQL

Stored Procedures can be defined as the set of SQL statements that are stored in the server. The users can refer from the stored procedure and does not have to write individual statements. Stored Procedures is a tool that is used to perform any specific operations like Insert, Update or Delete in our database recursively and it can be used to alter or update any records in database. Unless function that returns only single value, stored procedures can return zero and many values at a time.

It is an entity that is pre-compiled i.e. it is compiled only once and the compiled format is saved and executed every time it is called. It is mostly used when client application uses different languages or work on different platforms. Stored Procedures not only enhances the code reusability but also increases the performance by reducing traffic as information sent over network is lessened.

Features of Stored Procedures

• It can return any value. i.e. zero, single or multiple values.
• It can return XML data type.
• It can alter the records in database.
• It can have both input and output parameters.
• It can used SELECT and well as use Data Manipulation Language (DML) Statements.

Types of Stored Procedures

Now, while learning Stored Procedure vs Function in this article we can say that it has two types of stored procedures.

1. System Stored Procedures
2. User Defined Procedures

(i)System Stored Procedures

It is used to do informational tasks or admin level activities in the SQL server. Also, If you perform any type of modification or alteration in the database, you need to create a backup or do some administrative-level tasks that modified the area of database.

(ii)User Defined Procedures

It is stored and compiled in our SQL server. It consists of three types:

• User Stored Procedures
• User Defined Functions
• Triggers

How to Write A Stored Procedure in SQL

First, we will create a table tab_Student having entries Studentid, FirstName, LastName and Email. Then, we will create a procedure GetStudentName with input values (and output if required)

Creating Stored Procedure
Create PROCEDURE GetStudentName
(
@Studentid INT,
@Studentnamevarchar(100)
)
AS
BEGIN
SELECT @studentname =FirstName + ‘ ’ + LastName FROM tab_Student WHERE studentid=@studentid
END

Function Vs. Stored Procedure in SQL Server

Now, let us understand difference between function and stored procedure SQL server & how they are different from each other.

Conclusion:

In this blog, we learned about Functions and Stored Procedures and how they differ from each other. Reading this, users can easily understand both along with their uses in depth. Hence, there is nothing else left for users to learn about the difference between Stored Procedure and Function for sure.

FAQs

Q-1. What is the biggest drawback of stored procedures?

Ans: The major drawback of the stored procedure is that along with the number of stored procedures in the application, memory also increases as well. This is why when we learn about Stored Procedure vs Function, we understand why both have their own advantages & drawbacks.

Q-2. Are stored procedures faster than functions or it is just a myth?

Ans: Yes, It’s true that stored functions are faster than functions because these are already stored which saves time while execution.

Q-3. Can we call Stored Procedure from function?

Ans: No, we can’t. Functions are designed to return values based on input parameters, while stored procedures are used to perform a sequence of operations. Hence, the direct calling of a stored procedure from a function is not typically allowed.

Q-4. What is the difference between function and stored procedure and trigger in SQL Server?

Ans: Users can “execute” a stored procedure when required & call a “function” when necessary. However, when we discuss Triggers, we can say that users can execute Triggers automatically on specialized actions in a table such as update, delete, etc.