Difference between stored procedure and functions in SQL

Category: SQL Comments: No comments


Store Procedure Function
S.P need not be return a value Functions MUST return a value
S.P can be called independently usingexec keyword whereas Function is called using SELECTstatements.

 

S.P can be used for performing business logic

 

Functions are used forcomputations
Can be used EXEC inside an S.P

EXEC command can’t be used inside a Function
S.P takes IN and OUT parameters. Function parameters are always IN, no OUT is possible

 

Transaction related statement can be handled in S.P Can’t be handled in function.

 

XML parameters passed in S.P Can’t pass XML Parameters
S.P can affect the state of the database by using insert, delete, update and create operations. Functions cannot affect the state of the database which means we cannot perform insert, delete, update
Parsed and Compiled during design time itself Compiled and executed during run time

Leave a Reply

Your email address will not be published. Required fields are marked *

*

HTML tags are not allowed.