What Are Stored Procedures in SQL? – Explained with Examples

Here's the cleaned-up version of your code without emojis: ```html

Introduction

In SQL, text data types are used to store alphanumeric values like names, addresses, emails, and descriptions. Choosing the correct text type — CHAR, VARCHAR, or TEXT — is important for optimizing storage space, query speed, and database performance.

In this section, you'll learn the definitions, differences, and best use cases for each text data type.




1. CHAR (Fixed-Length String)

CHAR is used to store fixed-length strings. If the stored string is shorter than the defined length, SQL automatically pads it with spaces to match the specified size.



Features:

  • Fixed length
  • Fast and predictable performance
  • Uses extra storage if the data is often shorter than the specified length


Syntax:


column_name CHAR(length);

length = number of characters (1 to 255 depending on the database system)



Example:


CREATE TABLE countries (
country_code CHAR(2),
country_name CHAR(50)
    );

country_code like 'US', 'IN', 'UK' will always take 2 characters.



When to Use CHAR:


  • Data with a constant size, such as country codes, gender ('M', 'F'), state abbreviations
  • Fixed-format fields like credit card types ('VISA', 'MC')
  • When exact storage size is known and consistent



2. VARCHAR (Variable-Length String)

VARCHAR stands for Variable Character. It stores variable-length strings, meaning only the actual characters are stored without unnecessary padding.



Features:


  • Variable length
  • More space-efficient than CHAR for varying-length text
  • Slightly slower than CHAR when processing large volumes (because of extra calculations for string lengths)


Syntax:


column_name VARCHAR(length);

length = maximum number of characters allowed



Example:


CREATE TABLE employees (
first_name VARCHAR(50),
email VARCHAR(100)
    );

Names and emails can vary in length, making VARCHAR ideal.



When to Use VARCHAR:


  • Data with unpredictable or variable length
  • Names, emails, addresses, and descriptions under 255-65535 characters
  • Most general-purpose text fields




3. TEXT (Large Text Field)

TEXT is used to store large amounts of text like long descriptions, blog posts, comments, or articles.



Features:

  • Meant for large text storage (up to 65,535 characters for standard TEXT in MySQL)
  • Cannot have a default value (in some databases like MySQL)
  • TEXT fields are stored outside the main table with a pointer reference
  • Different variants exist (TINYTEXT, MEDIUMTEXT, LONGTEXT) for various sizes


Syntax:


column_name TEXT;


Example:


CREATE TABLE articles (
id INT,
title VARCHAR(255),
body TEXT
   );

body will store the full article content, which can be very large.



When to Use TEXT:


  • Long-form text fields (comments, articles, reviews, reports)
  • Data that exceeds normal VARCHAR limits
  • When exact storage requirements are unknown or potentially very large



Quick Comparison: CHAR vs VARCHAR vs TEXT


Feature CHAR VARCHAR TEXT
Storage Fixed length Variable length Variable, large storage
Max Size Up to 255 chars 65,535 bytes (typically) 65,535+ chars (depends on type)
Performance Fast for fixed-size Efficient for variable text Slightly slower for queries
Indexing Full index support Full index support Limited in some DBs
Best Use Case Codes, fixed formats Names, addresses, emails Articles, long descriptions



Important Tips


  • Use CHAR only when all values will be exactly the same length
  • VARCHAR is the best choice for most standard text fields
  • Reserve TEXT for content that exceeds VARCHAR limits
  • Consider VARCHAR(MAX) in SQL Server for large text that might need indexing
  • Be aware that TEXT fields may have limitations on default values and full-text indexing


Definition: What Is a Stored Procedure?

A stored procedure is a precompiled group of one or more SQL statements stored and executed on the database server. Stored procedures allow developers and database administrators to encapsulate logic, reuse code, and improve performance and security.

Think of a stored procedure as a function in programming—reusable, organized, and efficient.

Key Features of Stored Procedures

  • Reusable: Write once, run many times
  • Secure: Control access to underlying tables
  • Efficient: Reduces network traffic by executing code server-side
  • Modular: Break complex operations into manageable parts
  • Maintainable: Easier to debug, modify, and track changes

Basic Syntax (MySQL Example)

DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN cust_id INT)
BEGIN
  SELECT * FROM orders WHERE customer_id = cust_id;
END //

DELIMITER ;

Explanation:

  • CREATE PROCEDURE creates the procedure
  • IN cust_id INT is an input parameter
  • SQL statements are placed within BEGIN and END
  • DELIMITER // is used to handle semicolons within the procedure

How to Call a Stored Procedure

CALL GetCustomerOrders(5);

Types of Parameters in Stored Procedures

Parameter Type Description
IN Input value passed to the procedure
OUT Outputs a value back to the caller
INOUT Both input and output functionality

Benefits of Stored Procedures

Benefit Description
Performance Boost Executes on the server side, reducing network load
Code Reusability Avoids writing the same SQL repeatedly
Improved Security Can restrict direct table access and expose only procedures
Consistency Ensures business logic is centralized and consistent
Better Error Handling Use control-flow structures like IF, CASE, and LOOP

When to Use Stored Procedures

  • Complex business logic that requires multiple SQL operations
  • Frequently executed SQL routines (e.g., monthly reports)
  • Controlled access to sensitive data
  • Data validation and cleanup operations

Limitations of Stored Procedures

  • Harder to version control compared to application code
  • Debugging can be more complex
  • Overusing them can lead to tightly coupled logic in the database layer
  • Not ideal for highly dynamic logic (can be harder to maintain)
```