SQL declare variable: Scripting Basics

• 22.12.2025
• Blog
• Ethan Martinez
• 0

Ever wonder how to store temporary data while working with SQL scripts? Let me introduce you to a handy little feature called declaring variables. It’s simple, it’s powerful, and it makes your SQL life way easier. Whether you’re writing a small query or an entire script, variables can help you manage your data flow much more cleanly.

TL;DR Summary

In SQL, you can declare variables to store temporary values and reuse them within your scripts. This helps make your queries cleaner and easier to manage. Use the DECLARE keyword, assign values with SET or SELECT, and let your script do the heavy lifting. Variables come in many data types like INT, VARCHAR, and DATE.

---

What is a Variable in SQL?

A variable is like a container. It holds a value temporarily while your SQL script runs. Imagine you’re baking a cake — variables are like mixing bowls. You put stuff in, mix, and use it later.

SQL variables help you:

• Store results between steps.
• Make calculations reusable.
• Control flow in stored procedures or functions.

Neat, right?

Declaring Variables: Start with DECLARE

The first thing you need is the DECLARE keyword. That’s how SQL knows you’re creating a variable.

DECLARE @MyVariable INT;

This tells SQL: “Hey, I want a variable called @MyVariable, and it’ll hold an integer.”

You always start variable names with an at symbol (@). That’s just SQL’s style.

You also tell it the data type. Some common types:

• INT – for numbers without decimal places
• VARCHAR(N) – text, where N is the max length
• DECIMAL or FLOAT – for numbers with decimal places
• DATE or DATETIME – for date and time

Examples:

DECLARE @Count INT;
DECLARE @Name VARCHAR(50);
DECLARE @Price FLOAT;
DECLARE @Today DATE;

Setting a Value with SET

Now that you’ve declared the variable, let’s give it something to hold. Use the SET keyword:

SET @Count = 100;

Easy. Now @Count has 100 in it. You can also use SELECT to assign values.

SELECT @Name = 'Alice';

Both SET and SELECT work — use whichever fits the situation. Here’s a cool trick:

SELECT @Price = Price FROM Products WHERE ProductID = 1;

This pulls the Price for ProductID 1 and puts it into @Price.

Using Variables in Your SQL Scripts

Okay, you’ve declared and set your variables. Time to use them in a query!

SELECT * FROM Orders WHERE CustomerID = @Name;

This fetches orders for the customer whose name is in @Name.

Or use them in calculations:

SET @TotalCost = @Price * 2;

You can even mix variables and text:

PRINT 'The count is ' + CAST(@Count AS VARCHAR);

Use CAST() to convert between types if needed. SQL can be picky about that.

Can You Declare Multiple Variables?

You bet! Just separate them with commas or use multiple lines:

DECLARE @FirstName VARCHAR(50),
        @LastName VARCHAR(50),
        @FullName VARCHAR(100);

Or like this:

DECLARE @City VARCHAR(50);
DECLARE @State VARCHAR(50);

Whatever makes your brain happy!

Scope of Variables: Where They Live

Variables live only inside the batch or procedure where they’re declared. Once the script ends, poof! They’re gone.

Think of them like temporary helpers during a mission — once the mission completes, they vanish.

You can’t use a variable you declared in one place in an entirely different script unless you declare it again.

Common Mistakes to Avoid

Let’s save you some debugging time. Watch out for these:

• Forgetting to declare a variable before use.
• Trying to assign multiple values to a single variable.
• Confusing data types (e.g., storing text in an integer).
• Not converting types properly in calculations or string joins.

Debugging SQL can be a little harsh. Declaring and using your variables properly will save you lots of time.

Bonus: Using Variables in Stored Procedures

Variables really shine in stored procedures. You can do loops, conditionals, and track values inside one query file.

CREATE PROCEDURE GetDiscountedPrice
AS
BEGIN
    DECLARE @OriginalPrice FLOAT;
    DECLARE @DiscountRate FLOAT;
    DECLARE @FinalPrice FLOAT;

    SET @OriginalPrice = 200;
    SET @DiscountRate = 0.10;
    SET @FinalPrice = @OriginalPrice - (@OriginalPrice * @DiscountRate);

    PRINT 'Discounted price is: ' + CAST(@FinalPrice AS VARCHAR);
END;

Run that procedure and feel like a SQL boss.

Helpful Tips

• Always name your variables clearly — you’ll thank yourself later.
• Use comments to keep track of what each variable does.
• Practice using variables in small scripts until it feels natural.

Practice Time!

Try it out yourself! Here’s a mini challenge:

1. Declare a variable called @Age as INT.
2. Set it to 25.
3. Write a SELECT statement that checks if it’s less than 30.

DECLARE @Age INT;
SET @Age = 25;
SELECT CASE 
          WHEN @Age < 30 THEN 'Young at heart!'
          ELSE 'Seasoned soul!'
       END AS AgeGroup;

If you see ‘Young at heart!’ then you totally got it. 🎉

Conclusion

SQL variables are simple, yet mighty. With just a few lines of code, you can store values, reuse them, and make your scripts way more flexible and readable.

Start with DECLARE, set values with SET or SELECT, and plug those variables into your queries. And remember: clear names and consistent practice make perfect.

Now go out there and script like a SQL superhero!