Difference between procedure and user defined function.

Posted by Rajni.Shekhar on 4/3/2012 | Category: Sql Server Interview questions | Views: 7583 | Points: 40

Post |

Interview FAQs |

Exclusive Questions |

Interview Home

Answer:

1) A procedure is a precompiled and function is not pre-compiled.
2) procedure may or may not return a value where as function must return a value.
3) procedure can return multiple values and function can not return multiple values.
4) you can use function in select, where or in case statement but procedures can not be used.
5) function can not call procedures inside it.

Asked In: Many Interviews | Alert Moderator

Bookmark It

< Previous : Does interface have access modifier?

Next > : What is join? How many types of joins?

Comments or Responses

Posted by: Itechsasi on: 4/5/2012 | Points: 10

1.Difference between pre-compiled and not pre-compiled?
2.which one is fast?
3.Can I pass multiple parameter in function?

Posted by: Pandians on: 4/6/2012 | Points: 10

Hi

I would like to respond to your questions...

Normally, when you submit a query to the sql server, It does some internal activities as follows
- Parsing
- Compiling
- Algibrizing
- Optimizing
1. It prepares Execution Plan for the submitted query and stored it in plan cache and Executing the execution Plan with calculated statistics, So, whenever we submit a query to the SQL Server, It don't want to prepare the Execution Plan by involving the activities as said above.

Pre-Compiled: Already Compiled and Prepared the Execution Plan and stored it in Plan Cache.So, Next time, The query don't want to compile the query. It'll use the Execution Plan which already been created and stored in Plan cache.

2. Its based on the query what we using.. But, Pre-compiled is the best. But, Every submitted query will be Compiled/Parsed/Algibrized/Optimized every FIRST time. So, The Pre-Compiled will not be applied always for all the situations :)

3. YES. You can use multiple parameters in Functions as well.

More Interview Questions by Rajni.Shekhar

Latest Interview Questions