• Register
262 points

Stored procedure in SQL

A stored procedure is a prepared SQL code that can be saved, so the code can be reused multiple times as need. So that, if anyone have an SQL query that he write over and over again, by saving it as a stored procedure, and then he can just call it to execute it.

Function in SQL

Function is a set of SQL instructions worked with input parameter and return values after processing the following instructions. Every time a function have to be compiled before executing it.

Difference between stored procedure and function

  • Function can return one value which is mandatory whereas procedure can return zero or n values.

  • Functions can have only input parameters whereas procedures can have input/output parameters for it.

  • Function allows only select statement in it whereas procedure allows select as well as DML statement in it.

  • Procedures cannot be called from function whereas functions can be called from procedure.

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

  • It is possible to go for transaction management in procedure whereas it is not possible to go in function.

  • Function can be embedded in a select statement whereas procedures can not be utilized in a select statement. Stored procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas UDF can be used.

  • Return tables of UDFs can be treated as another rowset which can be used in JOINs with other tables.

  • Inline UDF's can be used in JOINs and other Rowset operations such as can be though of as views that take parameters.

  • In Example:
    • Stored Procedure
      CREATE PROCEDURE procedure_name
      EXEC procedure_name; 
    • Function
      SELECT CONCAT('W3Schools', '.com');