SQL declare variable: Scripting Basics

Want to dip your toes into the world of SQL and writing scripts? Don’t worry, it’s easier than it sounds. One of the first things you’ll want to understand is how to declare a variable in SQL. It’s a small but mighty step toward mastering SQL scripting. Let’s make it simple—and fun!

TLDR

Declaring a variable in SQL is like giving your script a reusable little box to store data. You use the DECLARE keyword to create it and SET or SELECT to assign a value. Variables can hold numbers, text, or even dates. They help your script become dynamic and reusable, which is pretty awesome.

What Is a Variable Anyway?

Before we dive into SQL specifics, let’s talk about what a variable actually is. Imagine you’re working in a kitchen. You label a jar “Sugar” so you know what’s inside, right? A variable is like that label—it lets you name and store information that you’ll use later.

In SQL, a variable holds a piece of data. It could be a number, some text, or even a date and time. And just like in the kitchen, labeling things correctly will make your life way easier.

Why Use Variables in SQL?

You might be thinking, “Can’t I just write the actual value in the script?” And sure, you could. But what if that value changes often? Or you want to use it in multiple places?

This is where variables become your best scripting buddy.

• They make your code cleaner.
• They help avoid repetition.
• They allow for more flexible and powerful scripts.

Basic Syntax: How to Declare a Variable

SQL Server uses the DECLARE keyword followed by a name and a data type. Here’s the basic format:

DECLARE @YourVariableName DataType;

Let’s break it down:

• @YourVariableName – This is the name you give your variable. Note the @ symbol at the start. It’s required in SQL Server.
• DataType – Think of this like choosing the right container. You wouldn’t store soup in a paper bag, right? Choose the correct data type: INT for numbers, VARCHAR for text, DATE for calendar dates, etc.

Example:

DECLARE @FirstName VARCHAR(50);
DECLARE @UserAge INT;

Congrats! You just created two variables: one to store someone’s name and another to store their age.

Assigning Values to Variables

Okay, you’ve got your variables. Now let’s give them something to hold. You can do that in two ways:

1. Using SET
2. Using SELECT

Using SET

SET @FirstName = 'Alice';
SET @UserAge = 30;

This method is clear and simple. Use SET when assigning one value at a time.

Using SELECT

SELECT @FirstName = FirstName FROM Users WHERE UserID = 1001;

This is super handy when you’re pulling data directly from a database. Need someone’s name based on their user ID? SELECT has your back.

Printing Variable Values (Because We Love Feedback)

Sometimes, you just want to know what your variable is holding. In SQL Server, you can use:

PRINT @FirstName;
PRINT @UserAge;

Or for more detail, use concatenation:

PRINT 'Name: ' + @FirstName + ', Age: ' + CAST(@UserAge AS VARCHAR);

Notice that we had to convert @UserAge to a string using CAST. That’s because you can’t directly combine strings with numbers.

Combining Variables in a Script

Let’s say you’re writing a report. You want to calculate and show the total price of a couple of products. Here’s how you can do that:

DECLARE @Price1 DECIMAL(10,2);
DECLARE @Price2 DECIMAL(10,2);
DECLARE @TotalPrice DECIMAL(10,2);

SET @Price1 = 19.99;
SET @Price2 = 29.49;
SET @TotalPrice = @Price1 + @Price2;

PRINT 'Total Price: ' + CAST(@TotalPrice AS VARCHAR);

Isn’t that neat? You now have flexible, readable code you could reuse tomorrow or next year.

Using Variables in Queries

Here’s where your script starts to feel really smart. You can use variables right inside SQL queries!

DECLARE @TargetCategory VARCHAR(20);
SET @TargetCategory = 'Electronics';

SELECT * FROM Products
WHERE Category = @TargetCategory;

This makes filtering super easy. Want to look at a different category? Just change the value of @TargetCategory.

A Quick Note on Scope

Variables in SQL are like secret agents. Their scope (life and accessibility) is limited to the batch, procedure, or script block they live in. Once that block ends—poof—they’re gone.

So remember: If you need to use the same variable across different scripts or blocks, you’ll need to declare it again.

Common Mistakes to Avoid

Even the best scripters mess up sometimes. Here are some typical traps to watch for:

• Forgetting the @: Every variable in SQL Server starts with an @. Don’t forget it!
• Using the wrong data type: Don’t try to stuff a string into an INT. It won’t like it.
• Mixing up SET and SELECT: SET is best when assigning one value. SELECT shines when pulling from queries.
• Not casting your data: Want to combine a number with text? You’ll need to use CAST or CONVERT.

Bringing It All Together

Let’s tie things up with a full example. Here’s a simple script that summarizes how to declare, assign, and use variables.

DECLARE @FirstName VARCHAR(100);
DECLARE @LastName VARCHAR(100);
DECLARE @FullName VARCHAR(200);

SET @FirstName = 'Harry';
SET @LastName = 'Potter';

SET @FullName = @FirstName + ' ' + @LastName;

PRINT 'Wizard Name: ' + @FullName;

Tada! A magical full name printed just like that.

Final Thoughts

Variables are a game changer in SQL scripting. You’ll write cleaner, smarter, and more maintainable code. Whether you’re filtering records, calculating values, or storing user inputs—variables are your trusty toolkit.

Start small and keep experimenting. The more you play, the more powerful your scripts will become. Who said scripting can’t be fun? Go ahead, declare some magic!