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
|
Good knowledge
ReplyDelete