Differences between Functions and Store Procedures in SQL Server

Below are the differences between functions and Store procedures in SQL Server

Functions Store Procedures
Non-deterministic functions are not allowed (Example: Getdate()) Non-deterministic functions are allowed
Function can have only input parameters SP’s can have either input, output parameters or with no parameters
Functions cannot be called outside the server remotely SP’s can be called outside the server
Functions can be embedded in the SQL statements anywhere in the WHERE/HAVING/SELECT section Procedures cannot be utilized in a select statement.
Functions are compiled and executed at run time Stored in parsed and compiled format in the database
Cannot affect the state of the database which means we cannot perform  delete, update and create operations on the database Can affect the state of the database by using insert, delete, update and create operations.
These are basically used to compute values. These are basically used to process the task.
Cannot change server environment and our operating system environment Can change server environment and our operating system environment
Can return one value which is mandatory Can return zero or n values
Allow only select statement in it Allow select as well as DML statement
Functions can be called from procedure Procedures cannot be called from function
Try-catch block cannot be used in a function Exception can be handled by try-catch block
We can’t go for Transaction Management We can go for transaction management
Leave a comment

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 )

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

%d bloggers like this: