What is the difference between stored procedure and stored functions?
Stored Procedures:
- A stored procedure is a set of SQL statements that can perform a task or execute a series of
actions.
- It does not necessarily return a value but can perform modifications to the database, execute
multiple operations,
and interact with multiple tables.
- A procedure is typically invoked by using the CALL statement.
Stored Functions:
- A stored function is designed to return a single value after execution.
- It is primarily used to perform computations and return results. Functions cannot make
permanent changes to the
database (they are limited to read-only operations in some databases).
- Functions are often invoked within SQL statements, including SELECT queries.
What is the difference between IN and OUT Parameters in stored Procedure.
IN Parameters:
- IN parameters are used to pass values into the stored procedure, allowing it to use these inputs
within its operations.
- They are read-only, meaning they cannot be modified within the procedure; the value of the
variable remains the
same throughout the execution.
OUT Parameters:
- OUT parameters are used to return values from the stored procedure to the caller.
- They are writable within the procedure, allowing the stored procedure to assign a value that can
be accessed after
the procedure execution is complete.
How a stored procedure and Stored function is being called or invoked?
Stored Procedure:
- A stored procedure is typically invoked using the CALL statement in SQL.
- Example: CALL procedure_name(parameter1, parameter2, ...);
Stored Function:
- A stored function is usually invoked within a SQL statement, such as SELECT, where it can
return a value that can be
used in the query.
- Example: SELECT function_name(parameter1, parameter2, ...);
