Stored Procedures and User Defined Functions

Posted on Updated on

yhhStored Procedures and User Defined Functions

Difference between Stored Procedures and User Defined Functions in SQL Server

 Stored Procedure 
Stored Procedures are pre-compile objects which are compiled for first time

and saved, which executes (compiled code) whenever it is called. Stored procedure does not necessarily return data, but it can return more than one result set. It can be used to affect data (update/insert/delete), can have transactions, can have error handling, do not have restrictions about non-deterministic functions, and can call other stored procedures.

User Defined Function
User defined function is compiled and executed every time when it is called. Function attempts to return something, always, and has several restrictions – for example, you can’t use DML statements, call stored procedures, call NEWID(), etc. You also cannot have error handling, transactions, or non-deterministic functions (e.g. GETDATE() in SQL Server 2000).

Differences :

  • Stored procedures compiled first time and reuse the execution plan when next time called while function compiled every time when called.
  • Stored procedure can have input parameter as well as output parameter while user defined function can have only input parameters.
  • Stored procedure can return zero or n values while user defined function must return a value.
  • You can use SELECT as well as INSERT/UPDATE/DELETE (DML) statement in stored procedure whereas in User defined function you can use only SELECT statement.
  • Try-catch block as well as Transactions can be used in a stored procedure whereas neither try-catch block nor Transactions can be used in user defined function.
  • You can call use user defined function in SELECT statement as well as in theWHERE/HAVING section, cause it must return a value, while stored procedures can not be used in a SELECT statement as well as in the WHERE/HAVING section, cause it may or may not return value.
  • You can call user defined function in a stored procedure whereas stored procedures cannot be called within user defined function.
  • Stored procedure can use table variable as well as temporary table while user defined function can use only table variables, it will not allow using temporary tables.
  • User defined function can be used in join clause as a result set while stored procedures can’t be used in Join clause.

*****************************************************************************************************

For more details and queries please feel free to email, visit or call us. Wishing you the very best for all your future endeavors.
Helpline: 9814666333, 8699444666
Email: info@technocampus.co.in

Please fill the form and we shall contact you soon.

Advertisements

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 )

Connecting to %s