Function vs procedure decision in SQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

When to use a function instead of a procedure?

Which scenario best fits the use of a SQL function rather than a procedure?

A You want to run a batch job that sends emails and logs results.

B You want to perform multiple data modifications without returning a value.

C You need to return a single value that can be used in a SELECT statement.

D You need to execute a series of commands that include transaction control.

💡 Hint

Functions return values and can be used inside queries.

Functions are designed to return a value and can be called within SQL statements like SELECT. Procedures are better for performing actions without returning values or for complex operations.

❓ query_result

intermediate

Output of calling a function in a SELECT

Given the function below, what is the output of the query SELECT calculate_tax(100);?

SQL

CREATE FUNCTION calculate_tax(amount DECIMAL) RETURNS DECIMAL DETERMINISTIC BEGIN RETURN amount * 0.1; END;

A 0.1

B 10.0

C 100.0

D Syntax error

💡 Hint

The function returns 10% of the amount.

The function multiplies the input by 0.1, so 100 * 0.1 = 10.0.

📝 Syntax

advanced

Identify the syntax error in procedure declaration

Which option contains a syntax error in the procedure declaration?

SQL

CREATE PROCEDURE update_salary(emp_id INT, new_salary DECIMAL) BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id; END;

A CREATE PROCEDURE update_salary(emp_id INT, new_salary DECIMAL) AS BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id; END;

B CREATE PROCEDURE update_salary(emp_id INT, new_salary DECIMAL) BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id; END;

C CREATE PROCEDURE update_salary(emp_id INT, new_salary DECIMAL) BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id END;

D ;DNE ;di_pme = di EREHW yralas_wen = yralas TES seeyolpme ETADPU NIGEB )LAMICED yralas_wen ,TNI di_pme(yralas_etadpu ERUDECORP ETAERC

💡 Hint

Check the keywords used in procedure declaration.

The keyword 'AS' is not used in standard SQL procedure declarations; it causes a syntax error here.

❓ optimization

advanced

Choosing between function and procedure for performance

You need to perform a calculation repeatedly inside a large SELECT query. Which choice is best for performance?

A Use a procedure because it can handle multiple statements efficiently.

B Use a function because it can modify data during the SELECT.

C Use a procedure because it avoids recalculating values.

D Use a function because it can be inlined and called within the SELECT.

💡 Hint

Functions can be called inside queries and sometimes optimized by the database engine.

Functions are designed to be called within queries and can be optimized or inlined, improving performance for repeated calculations.

🔧 Debug

expert

Why does this function cause an error when called?

Consider this function definition and call:

CREATE FUNCTION get_employee_name(emp_id INT) RETURNS VARCHAR(100) DETERMINISTIC BEGIN SELECT name FROM employees WHERE id = emp_id; END;

What error occurs when running SELECT get_employee_name(5);?

A Function does not return a value error

B Syntax error in function body

C Type mismatch error

D No error, returns employee name

💡 Hint

Functions must explicitly return a value.

The function uses SELECT but does not assign or RETURN the value explicitly, causing an error about missing return value.