Click here to hide categories Click here to show left categories

User: Home          welcome : Guest          Log In / Register here     




What is the difference between Stored Procedures and Function ( SQL Server interview question with answers)?

 

Stored Procedure

Functions

Compilation

Stored in database in compiled format.

Note: Compiled indicates, Execution plan will be made by sql at the time it created and stored in DB.

Will compiled at run time

Return type

·         It can directly return only integers

·         Return type is not must

·         It can return any scalar or table

·         Return type is must

Multiple return values

It can also return more than one values (of any data type) indirectly with the help of out parameters

It won’t support out parameters

DML Statements

Can have DML statements.

Cannot have DML statements.

Note: In case of multi-table valued functions it can contain DML statements affecting Table Variables.

Execution

·         Stored procedure can execute function.

·         Cannot be the part of Select query as a column.

·         Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT

·         Function cannot execute stored procedure.

·         Can be the part of select query as a column.

·         Functions be used in the SQL statements anywhere in the WHERE/HAVING/SELECT

Exception handling

Can have Try….Catch

Cannot have Try…Catch


Sometimes we face a question, why we can’t execute stored procedure inside a function?
Answer: 
  1. Stored Procedure may contain DML statements.
  2. Function can’t contain DML statements.
    So executing Function inside stored procedure will never break rule 1.
    But executing stored procedure inside function may break rule no 2.
So ultimately strict rule is made why Sql team, we can’t execute stored procedures inside function.

We are thankful to questpond.com for providing this SQL Server interview question with answers.

Below is a nice SQL Server interview question video on Unique key vs primary keys.

Helpful Website Url
http://www.questpond.com/
Share this article   |    Print    |    Article read by 612 times
Author:
Shivprasad koirala Koirala
I am a Microsoft MVP for ASP/ASP.NET and currently a CEO of a small E-learning company in India. We are very much active in making training videos , writing books and corporate trainings. Do visit my site http://www.questpond.com for .NET, C# , design pattern , WCF , Silverlight , LINQ , ASP.NET , ADO.NET , Sharepoint , UML , SQL Server training and Interview questions and answers
Related Articles: No related article
Related Interview Questions: No related interview question