Difference between Function and Stored Procedure?
Answer Posted / 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 |
Post New Answer View All Answers
How to disconnect from a sql server using mssql_close()?
What are dml triggers and types of dml triggers?
What is the definition for sql server 2000?
How to create function with parameter in sql server?
How is a full-text index updated?
how to invoke a trigger on demand? : Sql server database administration
What are the two authentication modes in sql server?
Can you force a query to use a specific index?
How can I add Reporting Services reports to my application?
You have a stored procedure, which execute a lengthy batch job. This stored procedure is called from a trigger you do not want to slow the data entry process you do not want trigger to wait for this batch job to finish before it completes itself what you can do to speed up the process?
what are the different ways of moving data/databases between servers and databases in sql server? : Sql server database administration
How are the exceptions handled in sql server programming?
What are the difference between “where” and “having” clause in sql server?
Is there any difference between primary key and unique with the not null condition?
last function used in MS Access to convert sql what function will use in sql