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

Question: Can I assign values to several T-SQL variables in one statement?

Answer: Yes. I remember answering this question years ago when inline initialization still felt new to many SQL Server users. Three small examples make the distinction clearer than a rule to memorize. Each example below produces 1 and One.

Method 1: separate SET statements. This is explicit and useful when assignments happen at different times.

DECLARE @ID1 int;
DECLARE @ID2 varchar(100);
SET @ID1 = 1;
SET @ID2 = 'One';
SELECT @ID1 AS ID1, @ID2 AS ID2;
GO

Method 2: one SELECT assigning both variables. This answers the interview question directly when the expressions are known values.

DECLARE @ID1 int, @ID2 varchar(100);
SELECT @ID1 = 1, @ID2 = 'One';
SELECT @ID1 AS ID1, @ID2 AS ID2;
GO

Method 3: initialize both variables in one DECLARE. For these fixed initial values, this is the short form I prefer.

DECLARE @ID1 int = 1, @ID2 varchar(100) = 'One';
SELECT @ID1 AS ID1, @ID2 AS ID2;
GO

These examples use constants, which keeps the answer simple. If you replace them with a SELECT from a table, you must think about cardinality: a multirow query can assign values repeatedly, and a query returning no rows leaves an existing variable unchanged. Do not use that form as a shortcut when you need a guaranteed single row or an ordered series of assignments.

If you want the history behind the syntax, I have earlier examples on declaring multiple variables, declaring and assigning in one statement, and inline variable assignment. The important interview distinction is between a concise way to initialize known values and the different behavior of a query that reads rows.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.