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, @ID2Method 2: New Style (and an Answer to this question)
DECLARE @ID1 INT, @ID2 VARCHAR(100);
SELECT @ID1 = 1, @ID2 = 'One';
SELECT @ID1, @ID2Method 3: My Preferred New Style (and an Answer to this question)
DECLARE @ID1 INT = 1, @ID2 VARCHAR(100) = 'One';
SELECT @ID1, @ID2Well, 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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
3 Comments. Leave new
Why SELECT rather than SET?
I learned along time ago to use SELECT, but could not remember. A quick google brought me to a decade old Dave article that explains it well: http://blog.sqlauthority.com/2007/04/27/sql-server-select-vs-set-performance-comparison/
To me, the right answer to this question would be:
Select @v1=col1, @v2=col2,….
from table