What is the difference between a function and a stored procedure?

Short answer: Function: Return Type: Always returns a value (can be scalar or table).

Explain a bit more

Used in Queries: Can be used in SELECT, WHERE, and ORDER BY clauses. Side Effects: Typically designed to perform calculations or return a result without modifying the database. Stored Procedure: No Return Type: May or may not return values (using output parameters or result sets). Used for Actions: Typically used for performing database operations (like INSERT, UPDATE, DELETE). Side Effects: Designed to perform operations that modify data or perform business logic.

Example code

Function (returns a value): CREATE FUNCTION GetEmployeeSalary (emp_id INT) RETURNS DECIMAL BEGIN RETURN (SELECT salary FROM employees WHERE id = emp_id); END; Stored Procedure (performs an action): CREATE PROCEDURE UpdateEmployeeSalary (IN emp_id INT, IN new_salary DECIMAL) BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id; END;

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.