This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Sunday, July 18, 2010

7 Differences Between SP and FN

1. Functions must return a value(scalar,inline table or multi statement table) whereas stored procedure may or may not retun a value.
2.Functions can return a table whereas stored procedures can create a table but can't return table.
3. Stored procedures can be called independently using exec keyword whereas function are called using select statements.
4. Stored procedure can be used to change server configuration(like setting permissions ) 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 stored procedure similarly a function can call another function and a stored procedure.The catch with function is that no user defined stored proc can be called.Only extended/system defined procs can be called.


Thanks..

No comments: