Friday, April 17, 2020

Difference between Stored Procedure and Function in SQL



Hello,

This post is for people who are beginners to SQL coding i.e. TSQL.
We know Stored Procedure and Function both execute set of code and reduce efforts to execute same code every time. Though both are used for same purpose there is difference in stored procedure and function, same discussed here. Once you go through it you will able to identify where to use them.



Stored Procedure
Function
Compilation
Compiled once when it is created and used first time. Same compiled format used every time until it gets recompiled.
Code inside function is compiled every time when you use it.
Return
Stored procedure may or may not return value.
Function must return value and at least 'NULL' when does not want to return any value.
Input and Output parameters
We can define both Input and Output parameters for Stored Procedure.
We can only define input parameter and not output parameter for function
Call each other
We can call function in stored procedure.
But we can’t call stored procedure in function.
Use in SELECT/WHERE/HAVING
We can’t use stored procedure in SELECT or in WHERE/HAVING clause.
But we can use function in SELECT or in WHERE/HAVING also.
e.g : SELECT GETDATE();
SELECT * from table where date < GETDATE();

Use of SELET and DMLs (Data Manipulation Language)
We can use all SELECT and DMLs INSERT/DELETE/UPDATE in stored procedure.
We can use only SELECT in function.
Transactions
We can use transactions in stored procedure.
We can’t use transactions in function.
Syntax to create and use
Check here
Check here

1 comment: