CodeStudy.net
Toggle Menu
Home
Online Java Compiler
Tutorials
Java Tutorial
Python Tutorial
SQL Database Design
Blog
All Posts
Stored Procedures and Functions in SQL
Evaluate your skill in writing reusable database routines.
1. What is a key difference between a SQL stored procedure and a SQL function regarding return values?
Functions return a single value, procedures use OUT parameters for results
Procedures return a single value, functions return multiple
Both return multiple values by default
Neither can return values
2. Which of the following are true characteristics of SQL stored procedures?
Can include transaction control statements (e.g., COMMIT, ROLLBACK)
Can be directly called within a SELECT clause
Can have IN, OUT, and INOUT parameters
Must return a value
3. A SQL function is allowed to perform an INSERT operation on a table.
True
False
4. What is the mandatory clause in a SQL function definition that specifies the return data type?
5. Which SQL construct can be invoked directly within a SELECT statement?
Stored procedure
Function
Both
Neither
6. Select all valid reasons to use a stored procedure instead of a function.
Need to modify database tables (e.g., UPDATE, DELETE)
Require returning multiple distinct values
Need to use the result in a JOIN clause
Require transaction management (e.g., SAVEPOINT)
7. SQL functions can declare OUT parameters to return values to the caller.
True
False
8. In MySQL, what statement is used to create a stored procedure? (Start with 'CREATE ...')
9. What is the primary purpose of using stored procedures and functions?
To reduce code repetition
To slow down query execution
To limit user access to tables
To increase network traffic
10. Which of the following are common features of both stored procedures and functions?
Can be stored in the database catalog
Can accept input parameters
Can return a result set
Can include conditional logic (e.g., IF-ELSE)
11. Stored procedures are precompiled, which can improve execution performance compared to ad-hoc queries.
True
False
12. In SQL Server, what keyword is used to execute a stored procedure?
13. Which SQL construct is required to return a value to the caller?
Stored procedure
Function
Both
Neither
14. Select all limitations of SQL functions compared to stored procedures.
Cannot modify database tables
Cannot use transaction control (e.g., COMMIT)
Cannot accept input parameters
Cannot be called from application code
15. A stored procedure can call another stored procedure within its definition.
True
False
Reset
Answered 0 of 0 — 0 correct