What is the difference between stored procedure and function in sql server?

Stored Procedre:

Stored Procedure is a group of sql statements that has been created once and stored in server database. It’s pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance. (Read more Here)

Function:

Function is not pre-compiled object it will execute every time whenever it was called.

Difference between Stored Procedure and Function

1) Procedure can return zero or n values whereas function can return one value which is mandatory (Read more Here).

2) Procedures can have input, output parameters for it whereas functions can have only input parameters.


3) Procedure allows select as well as DML(
INSERT/UPDATE/DELETE) statements in it whereas function allows only select statement in it.


4) Functions can be called from procedure whereas procedures cannot be called from function.


5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.


6) We can go for transaction management in procedure whereas we can’t go in function.


7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s