SELECT Into Variable When Multiple Rows Returned - SQL Server

When you need to retrieve a single row from a table or query, you can use the following syntax in SQL Server:

   DECLARE @name VARCHAR(30);
   SELECT @name = city FROM cities;

But what happens if SELECT returns multiple rows?

Assume we have the following table definition and data:

  CREATE TABLE cities (city VARCHAR(30));
 
  INSERT INTO cities VALUES ('St. Louis');
  INSERT INTO cities VALUES ('San Diego');
  INSERT INTO cities VALUES ('Seattle');

Let's run SELECT and output data:

   DECLARE @name VARCHAR(30);
   SELECT @name = city FROM cities;
   PRINT @name;
 
   -- Prints Seattle

SQL Server does not raise an error, and returns the last value. It defines how many rows meet the search criteria, and you can obtain this number using @@ROWCOUNT:

   DECLARE @name VARCHAR(30);
   SELECT @name = city FROM cities;
   PRINT @@ROWCOUNT;
 
   -- Prints 3

Note that when you want to retrieve one row, but multiple rows may meet the search condition, you have to check @@ROWCOUNT > 0, not @@ROWCOUNT = 1 to define if a row exists:

   DECLARE @name VARCHAR(30);
   SELECT @name = city FROM cities;
 
   IF @@ROWCOUNT = 1
    PRINT @name;
 
   -- Nothing is printed as @@ROWCOUNT is equal to 3
 
   SELECT @name = city FROM cities;
 
   IF @@ROWCOUNT > 0
    PRINT @name;
 
   -- Prints Seattle

Since SQL Server tries to find all rows (to calculate @@ROWCOUNT) while you just want to find the first row, consider using TOP 1 in the query:

   DECLARE @name VARCHAR(30);
   SELECT TOP 1 @name = city FROM cities;
   PRINT @name;
 
   -- Prints St. Louis

When you use TOP 1 you can safely check @@ROWCOUNT = 1 to define if a row was found.

• SQL Server White Papers, Articles and Reference