What is a Stored Procedure in SQL?
Learn what a stored procedure is, how it improves reuse and performance, and see a simple CREATE PROCEDURE example.
Expected Interview Answer
A stored procedure is a precompiled, named block of SQL statements saved inside the database that can be executed repeatedly with a single call, optionally accepting parameters.
Instead of an application sending the same multi-statement SQL logic over the network every time, that logic lives once inside the database and is invoked by name. The database parses and optimizes it in advance, so repeated calls run faster than re-sending raw SQL, and business logic (like validation or multi-table updates) stays centralized and reusable rather than duplicated across application code.
- Reduces network round trips for multi-statement logic
- Encourages reusable, centralized business logic
- Can improve performance via precompilation and caching
- Helps control access by granting execute rights, not raw table access
AI Mentor Explanation
Think of a stored procedure like a standard fielding drill a coach has written down once: "run to position, catch, throw to keeper." Instead of explaining the full drill to every player every session, the coach just calls out "Drill 3" and everyone executes the pre-defined sequence. A stored procedure works the same way — the multi-step SQL logic is saved once inside the database and triggered by a short call whenever needed.
Step-by-Step Explanation
Step 1
Define the procedure
Write CREATE PROCEDURE with a name, optional parameters, and the SQL statements to run.
Step 2
Store it in the database
The database compiles and saves the procedure so it is ready for repeated execution.
Step 3
Call the procedure
Invoke it by name (e.g. EXEC or CALL), passing any required parameters.
Step 4
Reuse across the application
Any authorized client or process can call the same procedure instead of duplicating the SQL logic.
What Interviewer Expects
- Clear definition of a named, precompiled block of SQL
- Understanding of parameters and reusability
- Awareness of performance and security benefits
- Ability to give a simple CREATE PROCEDURE example
Common Mistakes
- Confusing a stored procedure with a view or a function
- Forgetting that stored procedures can accept parameters
- Not mentioning reduced network round trips as a benefit
- Overusing stored procedures for simple single-statement queries
Best Answer (HR Friendly)
“A stored procedure is a saved block of SQL statements inside the database that you can run again and again by calling its name, instead of resending the full logic every time. It helps centralize business logic, reduces network traffic, and can improve performance since the database has already prepared it.”
Code Example
CREATE PROCEDURE GetOrdersByCustomer (@CustomerId INT)
AS
BEGIN
SELECT order_id, order_date, total
FROM Orders
WHERE customer_id = @CustomerId;
END;
-- Call the procedure
EXEC GetOrdersByCustomer @CustomerId = 101;Follow-up Questions
- What is the difference between a stored procedure and a function?
- What is the difference between a stored procedure and a view?
- How do stored procedures help prevent SQL injection?
- Can a stored procedure call another stored procedure?
MCQ Practice
1. A stored procedure is best described as?
A stored procedure bundles one or more SQL statements under a name so they can be executed repeatedly with a single call.
2. Which of these can a stored procedure accept to customize its behavior?
Stored procedures commonly accept input parameters, letting the same saved logic run differently per call.
3. What is a key performance benefit of stored procedures?
Because the logic is saved and precompiled in the database, calling it by name avoids resending full SQL text repeatedly.
Flash Cards
What is a stored procedure? — A named, precompiled block of SQL statements saved in the database and executed by name.
Can stored procedures take parameters? — Yes, they can accept input parameters to customize behavior per call.
Stored procedure vs function? — A function must return a value and can be used in a query; a procedure runs actions and is called independently.
Why use stored procedures? — To reuse logic, reduce network calls, and centralize business rules inside the database.