Fill multiple variables from one select statement in SQL Server

This may not happen very often, but occasionally you may need to fill multiple variables in a stored procedure with the results from a single SELECT statement. Of course, you can run the statement multiple times, but that’s kind of silly and pointless. The easier way is to use the following syntax. NOTE: THIS ONLY WORKS IF THE SELECT STATEMENT RETURNS ONE ROW.

 

DECLARE @VAR1 varchar(50)

DECLARE @VAR2 varchar(50)

DECLARE @VAR3 varchar(50)

 

SELECT @VAR1 = Field1, @VAR2 = Field2, @VAR3 = Field3 FROM SOMETABLE WHERE(SOMEFIELD=’SOMEVALUE’)

 

 

 

As long as you declare your variable to be of the correct type you should end up with the variables filled.

 

 

I had to use this in a trigger because of the need to pass the values to a stored procedure.

 

Later,
Gary