Difference between Function and Stored Procedure?

Answers were Sorted based on User's Feedback



Difference between Function and Stored Procedure?..

Answer / sreyasmn

1. Function must returned a value,but stored procedure is
not necessorly returned value

2. A function can be used to calculations,but sp used to
solve a business logic

Is This Answer Correct ?    36 Yes 3 No

Difference between Function and Stored Procedure?..

Answer / madhu

stored procedure when executed generates execution plan
once and next time when it is executed again it will use
the generated execution plan,it does not generate it again.

Function compiles from the beginning and prepares the trace
or execution each time it is called.

Is This Answer Correct ?    20 Yes 5 No

Difference between Function and Stored Procedure?..

Answer / sumesh.tc

stored procedure is precompiled one but Function not( want
to compile each time)

Is This Answer Correct ?    20 Yes 5 No

Difference between Function and Stored Procedure?..

Answer / raj..

The difference mejor between procedure and function is that
function must return value. that should be only single and
function can pass any no. parameter but come out only one
value only using return statement.

procedure can pass any parameter and came out any no. of
values there no any RETURN word.

Is This Answer Correct ?    4 Yes 1 No

Difference between Function and Stored Procedure?..

Answer / arunvs

stored procedure when executed generates execution plan
once and next time when it is executed again it will use
the generated execution plan,it does not generate it again.
sp used to solve a business logic

Function compiles from the beginning and prepares the trace
or execution each time it is called.function can be used to
calculations

Is This Answer Correct ?    5 Yes 3 No

Difference between Function and Stored Procedure?..

Answer / mohammad sajid

Although both functions and sp's are prcomiled sql
statements there exists some differences between them.

1. Functions must return a value(scalar,inline table or
multi statement table) whereas stored proc may or may not
retun a value.
2.Functions can return a table whereas stored procs can
create a table but can't return table.
3. Stored procs can be called independently using exec
keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration
(in terms of security-i.e. setting granular permissions of
user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions
whereas it can be with sp's.
6.transaction related statement can be handled in sp
whereas it can't be in function.
7. stored procedures can call a funtion or another sstored
proc similarly a function can call another function and a
stored proc.The catch with function is that no user defined
stored proc can be called.Only extended/system defined
procs can be called.

Hope this will be helpful and if there's any correction let
me know.

Is This Answer Correct ?    2 Yes 0 No

Difference between Function and Stored Procedure?..

Answer / deepak

1)Functions cannot affect the state of the database which
means we cannot perform insert,delete,update and create
operations on the database.
Stored Procedures can affect the state of the database by
using insert,delete,update and create operations.

2)Functions are basically used to compute values
stored procedures are basically used to process the task.

Is This Answer Correct ?    1 Yes 0 No

Difference between Function and Stored Procedure?..

Answer / yatendra

fuction used by mad people

while sp used by clever people

Is This Answer Correct ?    5 Yes 22 No

Difference between Function and Stored Procedure?..

Answer / arvind kumar

1-Function can not return the value but store procedure
return value
2-Store procedure is pree-compile statement but function is
not pree-compile satement.
3 function fallowed by select statement but store procedure
fallowed select, insert,update statement.

Is This Answer Correct ?    2 Yes 40 No

Post New Answer

More SQL Server Interview Questions

what is the system function to get the current user's user id? : Sql server database administration

0 Answers  


What do I need to start working with sql studio? : sql server management studio

0 Answers  


What is apply operator in sql?

0 Answers  


Why is sql server log file full?

0 Answers  


How we can refresh the view?

0 Answers  






Can we call SP inside a query?

5 Answers   CA, CarrizalSoft Technologies, Wipro,


how to delete duplicate rows in sql server2005

7 Answers   Cisco, CTS, HCL, IBM,


which database is best to use in oracle and sql server? explain reasons?

5 Answers   TCS,


Is null vs coalesce?

0 Answers  


as a part of your job, what are the dbcc commands that you commonly use for database maintenance? : Sql server database administration

0 Answers  


What is impersonation? What are the different impersonation options available in ssas? : sql server analysis services, ssas

0 Answers  


What is the purpose of floor function?

0 Answers  


Categories