diffrance between stored procedure & function in sql server?

Answers were Sorted based on User's Feedback



diffrance between stored procedure & function in sql server?..

Answer / dev

Difference between function and stored proc:
========================================================

Stored Procedure :supports deffered name resoultion Example
while writing a stored procedure

that uses table named tabl1 and tabl2 etc..but actually not
exists in database is allowed

only in during creation but runtime throws errorFunction
wont support deffered name

resolution.
2. Stored procedure returns always integer value by default
zero. where as function return

type could be scalar or table or table values(SQL Server).
3. Stored Procedure is pre compiled exuction plan where as
functions are not.
4. Stored Procedure retuns more than one value at a time
while funtion returns only one

value at a time.
5. We can call the functions in sql statements (select max
(sal) from emp). where as sp is

not so
6. Function do not return the images,text whereas sp
returns all.
7. Function and sp both can return the values. But function
returns 1 value only.
procedure can return multiple values(max. 1024) we can
select the fields from function. in

the case of procdure we cannot select the fields.
8. Functions are used for computations where as procedures
can be used for performing

business logic
9. Functions MUST return a value, procedures need not be.
10. You can have DML(insert, update, delete) statements in
a function. But, you cannot call

such a function in a SQL query..eg: suppose, if u have a
function that is updating a table..

you can't call that function in any sql query.
- select myFunction(field) from sometable;
will throw error.
11. Function parameters are always IN, no OUT is possible

Is This Answer Correct ?    5 Yes 0 No

diffrance between stored procedure & function in sql server?..

Answer / pramod prakash

Function can be used in an expression but we cann't use
procedure in an expression

Is This Answer Correct ?    3 Yes 0 No

Post New Answer

More ASP.NET Interview Questions

1.What r collections? 2.What is .pdb file? 3.Is it possible to provide access to users in master page? 4.What is dirty n Phantom Read(SQL)? 5.What r different isolation levels(SQL)? 6.How to set authentication mode in web.config file?

0 Answers   TCS,


Suppose you want a certain ASP.NET function executed on MouseOver overa certain button. Where do you add an event handler?

3 Answers   Visual Soft,


What are the advantages of using Master Pages?

0 Answers   MCN Solutions,


How to execute a stored procedure.and how to call it form a asp page

2 Answers  


how to encrypt a connection string in web.config file?

3 Answers   Hexaware,


Explain the use of errorprovider control in .net?

0 Answers  


Difference between asp and asp.net ?

4 Answers   Accenture, BirlaSoft, TCS,


How to store a dataset in a viewstate?

3 Answers   Patni,


How can you pass multiple complex types in Web API?

0 Answers  


Will the asp.net validators run in server side or client side? How do you do client-side validation in .net?

0 Answers  


What is postback request?

0 Answers  


What is http session state?

0 Answers  


Categories