Comparing Functions and Stored Procedures in SQL Server
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 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
- System Defined Function
- 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. 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.
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 I will discuss about stored procedures in SQL
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
It has two types of stored procedures
- System Stored Procedures
- User Defined Procedures
(i)System Stored Procedures
It is used to do informational tasks or admin level activities in the SQL server. 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
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.
|It returns single value.||It returns single, multiple values, and even zero.|
|It takes only input values.||It takes both input and output values.|
|It cannot modify database.||It can modify database.|
|It can be called from procedure.||It cannot be called from function.|
|It allows SELECT statements only.||It allows both SELECT and DML statements.|
|Function needs to be compiled and executed every time.||Procedures reuse the execution plan and do not needs to be compiled repeatedly.|
|Transaction management is not possible in function.||Transaction management is not possible in stored procedures.|
In this blog, I try to explain you about Functions and Stored Procedures and how they differ from each other. I hope, after reading this you will be able to understand them.