Difference between SQL Server Functions and Stored Procedures

Sql Server has Stored procedures and Functions objects. They have their own limitations, advantages and disadvantages. Below is a list of differences between Functions and Stored Procedures in Sql Server.

Functions
Stored Procedures
Function must return a value.
Procedure can return zero or n values.
Functions can have only input parameters for it.
Procedures can have both input and output parameters in it.
Function allows only SELECT statement in it.
Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it.
Function can be embedded in a SELECT statement.
Procedures cannot be utilized in a SELECT statement.
Functions can be used in the SQL statements anywhere in the WHERE/ HAVING/ SELECT section.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/ HAVING/ SELECT section.
     Functions that return tables can be treated as another row-set. This can be used in JOINs with other tables.
Procedures cannot be used in a JOINs statement.
     Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
Procedures cannot be used in a JOINs statement.
Try-catch block cannot be used in a Function.
Exception can be handled by try-catch block in a Procedure.
Transactions are not allowed in Functions.
We can handle Transactions in Procedure.
Function should have at least one input parameter.
Stored Procedure may take 0 to n input parameters.
Functions can be called from Procedure.
Procedures cannot be called from Function.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s