Function vs Procedure in Sql

 In this Blog, we are going to the the difference between the Functions and Procedures in Sql. This will be the common questions in most of the Sql interview. So trust me, by following this blog you will be able to get the crisp and clear idea about functions and procedure in sql and you will be able to answer the interview questions very easily.

Let us discuss the topic,

Function:

• Functions always return value after the execution.
  
  

• It can be called using Stored Procedures.
  
  

• A function used only to read data. ( you can relate this point with previous point , since it is used only to read data it can be used in Stored Procedure but vice-versa is not possible).
  
  

• It does not support try and catch blocks for error handling.
  
  

• Function can be operated in SELECT statement.
  
  

Let us look into the Syntax of Functions in sql server:

  CREATE FUNCTION schema_name.function_name (parameter_list)  

RETURNS data_type AS  

BEGIN  

    statements  

    RETURN value  

END     

The above syntax parameters can be grasped easily via below comments:

    - First the Function name must be defined using CREATE FUNCTION keyword. The name of the schema is optional. If we will not define the schema name, SQL Server uses default schema dbo.  

    - Next step is to define the list of parameters enclosed in parenthesis. Then RETURNS statement must be defined with  the data type of the return value.

    - In the next step, we will write the statements for the function between the BEGIN and the END block. In the last step, before the END statement we need to give RETURN statement with the value that needs to be returned.

Procedure:

• Procedure can return value using "IN OUT" and "OUT" Arguments.
  
  
• Stored Procedures cannot be called from functions.
  
  

• A Procedure can be used to read data and modify data.
  
  

• It  supports try and catch blocks for error handling.
  
  

• Function can be operated in SELECT statement.
  
  

Now let us look into Stored Procedure Syntax:

  

CREATE PROCEDURE schema_name.procedure_name  (

                @parameter_name data_type,   

                ....   

                parameter_name data_type  )

AS  

   BEGIN  

      -- SQL statements  

      -- SELECT, INSERT, UPDATE, or DELETE statement  

   END      

 

 

The above syntax parameters can be grasped easily via below comments:

    - First the Function name must be defined using CREATE PROCEDURE keyword. The name of the schema is optional. If we will not define the schema name, SQL Server uses default schema dbo. 

    - Next step is to define the  parameters enclosed in parenthesis with Data types.

    - In the next step, we will write the statements for the Procedure between the BEGIN and the END block. 

    Therefore from here, you can notice that update or delete is allowed in syntax of Stored Procedure, that is because Stored Procedure is allowed to read data as well as modify the data but Function cannot do that.


Hope this blog gives you a clear-cut idea about Functions and Procedures.

If you like this blog you can support us by clicking the follow button on the right side of this window. And you can follow us on the other social media as well that is given below the follow button.

Thank You !!!