How to Assign Multiple Variables in a Single T-SQL Query? – Interview Question of the Week #148

Question: How to Assign Multiple Variables in a Single T-SQL Query?

Answer: I do remember answering this question many years ago, when it was newly introduced in SQL Server. It has been so long that I have forgotten that in which version of SQL Server this particular feature was introduced.

Without further ado, let us see two different methods to assign variables in SQL Server.

Method 1: Old Style

DECLARE @ID1 INT;
DECLARE @ID2 VARCHAR(100);
SET @ID1 = 1;
SET @ID2 = 'One';
SELECT @ID1, @ID2

Method 2: New Style (and an Answer to this question)

DECLARE @ID1 INT, @ID2 VARCHAR(100);
SELECT @ID1 = 1, @ID2 = 'One';
SELECT @ID1, @ID2

Method 3: My Preferred New Style (and an Answer to this question)

DECLARE @ID1 INT = 1, @ID2 VARCHAR(100) = 'One';
SELECT @ID1, @ID2

Well, all of the above queries return the same results. They all displays result as 1 and one. However, you can clearly see why method 3 is my preferred way. It is short and efficient.

Here are blog posts earlier, I wrote which discusses the same subjects:

• SQL SERVER – DECLARE Multiple Variables in One Statement
• SQL SERVER – Declare and Assign Variable in Single Statement
• SQL SERVER – Inline Variable Assignment

Reference: Pinal Dave (https://blog.sqlauthority.com)