Difference between Stored Procedure and Function:
|1. Stored procedure may or may not return a value.||1. Function has to return a value.|
|2. Stored procedure may return any no.of values.||2. Function can return only one value.|
|3. Stored procedure supports DDL, DML, DQL, and DCL statements.||3. Function supports only DQL(i.e., select statements)|
|4. Stored procedure can not return a table.||4. Function can return a table.|
|5. Stored procedure can call another stored procedure.||5. Function can not call another stored procedure, but can call an extended stored procedure.|
|6. Error handling is allowed in stored procedure.||6. Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.|
|7. In stored procedures we can use dynamic SQL or temp tables and Table variables.||7. Functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.|
8. Stored procedure is executed with Exec statement.
Exec spAdd 10, 20
8. Function is executed with Select statement.
For Table-valued Function:
select * from dbo.GetEmployeesData()
For Scalar-Valued Function;
select dbo.Addition(10, 20)