Diference Between Stored Procedures And Functions

In this article, I will discuss the difference between Stored Procedures and Functions and their usage.

Introduction

 
Both stored procedure and functions, are database objects that contain a set of SQL statements to complete a specific task. But there are also some differences among them. We will discuss their differences and their usage in this article.
 

Stored Procedures

 
Stored procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved. It will execute whenever it is called. If you think of a query that you write over and over again, instead of writing that query each time you save your query as a stored procedure and whenever you want that query to be executed you just call saved store procedure. You can also pass parameters to the stored procedure. So, it becomes more flexible and you can do what your parameter say in your stored procedure.
 
Stored Procedures can also improve performance. Many tasks are implemented as a series of SQL statements. The logic applied to result of first SQL statements determine which subsequent SQL statements are to be executed. If these SQL statements and conditional logic are written into a Stored procedure, they become part of a single execution plan on the server. All of your work will be done on the Server which makes it faster.
 

Advantages of Stored Procedures

 
Performance
 
Stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users. This lowers memory requirements and makes application fast.
 
Productivity and Ease of Use
 
By passing parameters to stored procedures, you can handle your logic in stored procedures. You can use the IDE of your choice and then you can deploy them on any tier of network architecture. Moreover, they can be called by programmatic interfaces and development tools.
 
Security Controls
 
You can grant user permissions to execute a Stored Procedure independently of underlying table permissions.
 

Functions

 
Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. A function is compiled and executed every time whenever it is called. A function must return a value and cannot modify the data received as parameters.
 
There are two types of Functions,
  • Built-in functions
    Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference.

  • User-Defined Functions
    By using the CREATE FUNCTION statement, users can write their own Transact-SQL functions according to their specific need. User-defined need zero or more input parameters and return a single data value.

Advantages of Functions

 
Modular Programming
 
You can create a function once, store it in your database and use it whenever you need.
 
Faster Execution
 
Similar to Stored Procedures, Transact-SQL User-Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This makes it faster.
 

Difference between Store procedure and functions

  1. The function must return a value, but in Stored procedure it is optional. Even a stored procedure can return zero or n values.
  2. Functions can be called from Stored procedures while a Stored procedure cannot be called from a function.
  3. The procedure allows to write INSERT, UPDATE, DELETE statements with SELECT statement while function only allows SELECT statement.
  4. Procedures cannot be utilized in a SELECT statement while a function can be embedded in a SELECT statement.
  5. Stored procedures cannot be used in SQL statements like WHERE/HAVING/SELECT statement whereas functions can be used.
  6. We can use transactions in Stored procedures while transactions cannot be used in functions.

Code Examples

 
Printing Hello World in stored procedure vs. in function.
 
In the stored procedure,
  1. CREATE PROCEDURE HelloWorldprocedure  
  2. AS  
  3. PRINT 'Hello World'  
Executing stored procedure
  1. exec HelloWorldprocedure  
In function,
  1. CREATE FUNCTION dbo.helloworldfunction()  
  2. RETURNS varchar(20)  
  3. AS   
  4. BEGIN  
  5.      RETURN 'Hello world'  
  6. END  
Using functions,
  1. select dbo.helloworldfunction() as regards  

Using variable in Stored Priocedure vs. in Function

 
In the stored procedure,
  1. CREATE PROCEDURE CONVERTCELSIUSTOFAHRENHEIT  
  2. @celsius real  
  3. as  
  4. select @celsius*1.8+32 as Fahrenheit  
Executing a stored procedure,
  1. exec CONVERTCELSIUSTOFAHRENHEIT 0  
In function,
  1. CREATE FUNCTION dbo.f_celsiustofahrenheit(@celcius real)  
  2. RETURNS real  
  3. AS   
  4. BEGIN  
  5. RETURN  @celcius*1.8+32  
  6. END  
Using function,
  1. select dbo.f_celsiustofahrenheit(0) as fahrenheit     
In this article, we discussed what stored procedures and functions are, what are their differences, and how they are used.
 
Happy coding!!!