Declaring Variables in SQL: Practical Examples

Ever wondered how to store a value in SQL and use it later in your query? That’s where variables come in. They help you write cleaner, smarter, and more flexible SQL statements. Plus, they make your code easier to read!

Don’t worry if it sounds technical. In this article, we’ll explore how to declare and use variables in SQL with fun, simple examples. Ready? Let’s go!

What Is a Variable in SQL?

A variable is just a place to store data. Think of it like a labeled box. You put a value in it, give the box a name, and use that name whenever you need the stored value.

SQL lets you use variables in scripts, stored procedures, or functions. They’re super helpful when you need to reuse values or make your code more dynamic.

How to Declare a Variable

In most SQL dialects like SQL Server, you declare a variable using the DECLARE keyword. Here’s a simple format:

DECLARE @myVariable datatype;

Example:

DECLARE @cityName VARCHAR(50);

This creates a variable called @cityName that can store a string up to 50 characters long.

Assigning Values to Variables

Once you’ve declared a variable, you can assign a value using the SET command.

SET @cityName = 'New York';

Now, @cityName holds the value ‘New York’. Easy, right?

There’s also another way – assign values directly using SELECT:

SELECT @cityName = city FROM Cities WHERE city_id = 1;

This grabs the city name from your table and stores it in the variable.

Why Use Variables?

Let’s say you run a report every day for the same product ID. You can store that ID in a variable and reuse it in your queries.

Example:

DECLARE @productID INT;
SET @productID = 101;

SELECT * FROM Products WHERE ProductID = @productID;

Much better than typing 101 over and over again. Plus, if you need to change it, you only change it in one place!

Using Variables in Stored Procedures

Variables become even more powerful when used in stored procedures.

A stored procedure is like a tiny program inside your database. It can accept input, store results in variables, and return output.

Example: Here’s a basic stored procedure that uses a variable:

CREATE PROCEDURE GetProductName
    @prodID INT
AS
BEGIN
    DECLARE @prodName VARCHAR(100);

    SELECT @prodName = ProductName
    FROM Products
    WHERE ProductID = @prodID;

    PRINT 'The product name is: ' + @prodName;
END;

Now when you run EXEC GetProductName 200;, it will print the product name for ID 200.

Cool, right?

Multiple Variables

Need more than one variable? No problem! Just declare them one after another.

DECLARE @firstName VARCHAR(50);
DECLARE @lastName VARCHAR(50);
SET @firstName = 'Sam';
SET @lastName = 'Smith';

Then use them together:

SELECT @firstName + ' ' + @lastName AS FullName;

Output: Sam Smith

Common Mistakes to Avoid

• Forgetting to declare: Always declare your variable before using it.
• Misspelling the name: SQL is picky! @Name and @name are different.
• Wrong data type: Want text? Use VARCHAR. Want numbers? Use INT or DECIMAL.

When in doubt, test with PRINT or SELECT to see what your variable actually contains.

Recap

• Use DECLARE to create a variable.
• Use SET or SELECT to assign a value.
• Use the variable wherever you’d normally use that value.

Variables are like tools in your SQL toolbox. They help you build dynamic, reusable code without a mess.

So next time you write a query, remember your handy variable friend. Your future self will thank you!