Detailed explanation
A stored procedure is a named routine stored in the database that groups SQL statements and, where supported, procedural logic such as conditions, loops, and exception handling. Applications invoke it to perform an operation, such as updating salaries or processing an order. Procedures can accept input parameters, expose output parameters, and sometimes return multiple result sets. They help centralise database operations, reduce application-to-database round trips, and provide controlled access through execution permissions.
A function is primarily designed to compute and return a result. A scalar function returns one value and can usually appear in expressions, for example SELECT calculate_tax(salary) FROM employees. Some databases also support table-valued functions, which return rows and are used as table sources rather than scalar expressions. A procedure is normally invoked as a separate command: MySQL and PostgreSQL use CALL, while SQL Server commonly uses EXEC. Unlike a function, a procedure is not generally usable directly inside a SELECT expression.
In the example, raise_salary receives an employee ID and a percentage, then updates that employee’s salary. CALL raise_salary(5, 10) increases employee 5’s salary by 10%, assuming an existing row with a non-NULL salary. This is MySQL-style syntax, not portable SQL for every database. In MySQL, DECIMAL without an explicit scale defaults to zero fractional digits, so DECIMAL(5,2) is clearer if fractional percentages are allowed. A practical version should also validate inputs and decide how to handle a missing employee.
Under the hood, the database stores the routine definition and executes its statements using its query-processing machinery. Calling procedures “precompiled SQL” is a useful shorthand but not a universal guarantee: compilation, plan caching, and replanning differ by database. Restrictions on data modification and transaction control also vary; SQL Server functions restrict side effects, whereas PostgreSQL functions can perform DML. Interviewers therefore look for the core distinction—an operation-oriented procedure versus a result-producing function—supported by accurate, database-specific qualifications.
Key points to remember
- Procedures encapsulate database operations; functions compute and return results.
- Procedure invocation is database-specific: CALL in MySQL/PostgreSQL and commonly EXEC in SQL Server.
- Scalar functions can appear in SQL expressions; table-valued functions can supply rows.
- Procedures may expose OUT parameters or result sets, depending on the database.
- DML restrictions, transaction control, and execution-plan reuse depend on the database engine.
Common mistakes candidates make
- Claiming that every stored procedure is fully precompiled or automatically faster than equivalent SQL.
- Saying that functions can never modify data or can only return a single scalar value.
- Presenting CALL, parameter syntax, or transaction behaviour as identical across all databases.
Likely follow-up questions
Can a stored procedure return a value?
Yes, but the mechanism varies: procedures may expose output parameters, result sets, or a return status. For example, SQL Server supports an integer return code as well as output parameters and result sets.
Can a function update a table?
It depends on the database. PostgreSQL functions can execute DML, while SQL Server user-defined functions cannot modify persistent database state.
Are stored procedures always faster than application-issued SQL?
No; performance depends on query design, indexing, execution plans, and workload. Procedures can reduce network round trips, but parameterised SQL issued by an application can also benefit from plan reuse.
Interview tip: Lead with the operation-versus-result distinction, explain the salary example, and identify the database before discussing restrictions.