Why stored procedures are needed in SQL

Introduction

Stored procedures help run a set of database commands quickly and safely without typing them every time.

When you want to repeat the same database task many times easily.

When you want to keep your database safe by controlling how data is changed.

When you want to make your database faster by running pre-written commands.

When you want to share common tasks with many users without giving full access.

When you want to keep your database organized by grouping related commands.

Syntax

SQL

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END;

Use CREATE PROCEDURE to start making a stored procedure.

Put the SQL commands between BEGIN and END.

Examples

This procedure gets all records from the Users table.

SQL

CREATE PROCEDURE GetAllUsers
AS
BEGIN
   SELECT * FROM Users;
END;

This procedure deletes orders older than January 1, 2023.

SQL

CREATE PROCEDURE DeleteOldOrders
AS
BEGIN
   DELETE FROM Orders WHERE OrderDate < '2023-01-01';
END;

Sample Program

This creates a procedure to show product details and then runs it.

SQL

CREATE PROCEDURE ShowProducts
AS
BEGIN
   SELECT ProductID, ProductName, Price FROM Products;
END;

EXEC ShowProducts;

Important Notes

Stored procedures can accept inputs to make them flexible.

They help reduce errors by reusing tested code.

Using stored procedures can improve security by limiting direct table access.

Summary

Stored procedures save time by running saved commands.

They help keep data safe and organized.

They make your database faster and easier to manage.

Practice

1. Why are stored procedures useful in a database system?

easy

A. They slow down the database to prevent overload.

B. They make the database look colorful and pretty.

C. They allow users to change data without any rules.

D. They save time by running pre-written commands quickly.

Solution

1. Step 1: Understand stored procedures purpose

   Stored procedures are saved sets of commands that run quickly when called.

2. Step 2: Identify the benefit in options

   They save time by running pre-written commands quickly. says they save time by running pre-written commands, which matches the purpose.

3. Final Answer:

   They save time by running pre-written commands quickly. -> Option D

4. Quick Check:

   Stored procedures save time = A [OK]

Hint: Stored procedures run saved commands fast [OK]

Common Mistakes:

• Thinking stored procedures change database appearance
• Believing stored procedures allow rule-free data changes
• Assuming stored procedures slow down the system

2. Which of the following is the correct way to call a stored procedure named GetUserData in SQL?

easy

A. CALL GetUserData();

B. EXECUTE GetUserData;

C. RUN PROCEDURE GetUserData();

D. START GetUserData();

Solution

1. Step 1: Recall stored procedure call syntax

   Standard SQL uses the CALL keyword followed by procedure name and parentheses.

2. Step 2: Match syntax with options

   CALL GetUserData(); uses CALL GetUserData(); which is correct syntax.

3. Final Answer:

   CALL GetUserData(); -> Option A

4. Quick Check:

   CALL syntax = D [OK]

Hint: Use CALL to run stored procedures [OK]

Common Mistakes:

• Using EXECUTE without parentheses
• Using RUN PROCEDURE which is invalid
• Using START which is not SQL syntax

3. Consider this stored procedure that returns the count of users older than 30:

CREATE PROCEDURE CountOlderUsers()
BEGIN
  SELECT COUNT(*) AS UserCount FROM Users WHERE Age > 30;
END;

What will be the output when you run CALL CountOlderUsers(); if the Users table has 5 users older than 30?

medium

A. UserCount = 5

B. UserCount = 0

C. Syntax error

D. No output

Solution

1. Step 1: Understand the procedure logic

   The procedure counts users with Age > 30 and returns that count as UserCount.

2. Step 2: Apply given data

   Since 5 users are older than 30, the count returned will be 5.

3. Final Answer:

   UserCount = 5 -> Option A

4. Quick Check:

   Count of users older than 30 = 5 [OK]

Hint: Count matches condition in SELECT query [OK]

Common Mistakes:

• Assuming no output because procedure has no RETURN
• Thinking syntax error due to missing parameters
• Confusing count with total users

4. You wrote this stored procedure:

CREATE PROCEDURE UpdateSalary()
BEGIN
  UPDATE Employees SET Salary = Salary + 1000
END;

When you try to run CALL UpdateSalary();, you get an error. What is the likely problem?

medium

A. Procedure name cannot have uppercase letters

B. Missing semicolon after UPDATE statement

C. CALL keyword is incorrect

D. Salary column cannot be updated

Solution

1. Step 1: Check procedure syntax

   Each statement inside BEGIN...END must end with a semicolon.

2. Step 2: Identify missing semicolon

   The UPDATE statement lacks a semicolon at the end, causing syntax error.

3. Final Answer:

   Missing semicolon after UPDATE statement -> Option B

4. Quick Check:

   Semicolon missing = B [OK]

Hint: End each SQL statement with semicolon inside procedures [OK]

Common Mistakes:

• Thinking procedure names can't have uppercase letters
• Believing CALL keyword is wrong
• Assuming Salary column is read-only

5. A company wants to ensure that every time a new order is added, the total sales are updated automatically. Which benefit of stored procedures best explains why using a stored procedure for this task is a good idea?

hard

A. Stored procedures allow users to edit data without restrictions.

B. Stored procedures slow down the database to avoid errors.

C. Stored procedures help keep data safe and organized by controlling updates.

D. Stored procedures make the database design more colorful.

Solution

1. Step 1: Understand the task requirement

   The company wants automatic, controlled updates to total sales when orders are added.

2. Step 2: Match benefit of stored procedures

   Stored procedures help keep data safe and organized by controlling how updates happen, which fits the need.

3. Final Answer:

   Stored procedures help keep data safe and organized by controlling updates. -> Option C

4. Quick Check:

   Data safety and control = C [OK]

Hint: Stored procedures control and protect data updates [OK]

Common Mistakes:

• Thinking stored procedures allow unrestricted data edits
• Believing stored procedures slow down the system
• Confusing appearance with functionality