In this article I try to explain the difference between Stored Procedure and Function. I hope after reading this article you will be able to understand BASIC as well as ADVANCE difference between Stored Procedure and Functions1.Function must return a value.Stored Procedure may or not return values. 2 Will allow only Select statements, it will not allow us to use DML statements. Can have select statements as well as DML statements such as insert, update, delete and so on 3.It will allow only input parameters, doesn’t support output parameters. It can have both input and output parameters. 4 It will not allow us to use try-catch blocks.For exception handling we can use try catch blocks. 5 .Transactions are not allowed within functions.Can use transactions within Stored Procedures. 6 .We can use only table variables, it will not allow using temporary tables. Can use both table variables as well as temporary table in it. 7 .Stored Procedures can’t be called from a function.Stored Procedures can call functions. 8 .Functions can be called from a select statement. Procedures can’t be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure. 9 A UDF can be used in join clause as a result set. Procedures can’t be used in Join clause
In stored procedure you can perform DML operations. But in function you cannot. In stored procedure, return option is optional. But in function you should return some thing.
Function takes input & provide outputBut procedure not
Stored Procedure : 1.) Take input and output parameter 2.) Can return zero,single and multiple values 3.) Can execute function 4.) use transaction 5.) use try-catch fro exception 6.) can use DML statementFunction : 1.) Take only input parameter 2.) must return a value 3.) cannot execute stored procedure 4.) cannot use transaction 5.) cannot use try-catch 6.) use only select statement
1. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement. 2. UDF's cannot return Image, Text where as a Stored Procedure can return any data type. 3. UDF should return a value where as Stored Procedure need not. 4. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters. 5. Temporary Tables cannot be used in a UDF where as a Stored Procedure can use Temporary Tables. 6. User Defined Function does not support error handling where as Stored Procedure support error handling.