Declaring multiple variables and assigning values to them in a single statement

#0379 – SQL Server – Basics- Declaring multiple variables in a single statement


Making a switch between technologies is sometimes difficult and it always helps to establish parallels between them during the learning phase. Recently, I met someone who had worked on object-oriented programming languages like C# and had to start learning T-SQL in order to work on a new Agile project that was coming his way.

In order to help him get started, the first thing I did was to establish a parallel on how to declare new variables in a module/script. Just as one can declare more than one variable in a single statement in C#, one can do so in T-SQL.

This actually came as a surprise to a few of my team-mates, which is why I decided to write it up as a T-SQL basics post.

So, here’s how to declare multiple variables spanning multiple data-types in a single DECLARE statement:

USE tempdb;
GO
DECLARE @iVar1 INT = 10,
        @iVar2 INT = 05,
        @dVar  DECIMAL(19,4) = 10.05,
        @sVar  VARCHAR(20) = 'Ten';

SELECT @iVar1 AS IntegerValue1, 
       @iVar2 AS IntegerValue2, 
       @dVar  AS DecimalValue,
       @sVar  AS StringValue;
GO
Declaring multiple variables and assigning values to them in a single statement

Declaring multiple variables and assigning values to them in a single statement

Do keep in mind though that starting SQL Server 2008, the DECLARE statement can generate exceptions if you perform declarations and initialization/assignment in the same statement.

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s