Gotcha When Setting Variable

Did you know there is more than one way to set a variable in SQL Server? You can actually set a variable without using “SET” at all. Let’s look at an example that shows how setting a variable with SELECT can cause a headache when dealing with identical values.

One and Only?

What if we need to retrieve information for the user record with a “Chad C” DisplayName value from the StackOverflow2013 database? For the sake of this example, we’ll expect the Users table to only have one “Chad C” user. Let’s begin by declaring a variable and using SET to set the value.

DECLARE @UserId AS INT;

SET @UserId = (
		SELECT Id
		FROM Users
		WHERE DisplayName = 'Chad C'
		);

We didn’t make it very far.

Since our query can’t set @UserId to multiple values, we got the error message:

Msg 512, Level 16, State 1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If we run the query below we can confirm there are multiple ‘Chad C’ records in the Users table:

SELECT Id, Creationdate, DisplayName, Location
FROM Users
WHERE DisplayName = 'Chad C';
GO

Consistently Inconsistent

Knowing what we know now about the user data, would you expect to get a similar error no matter how @UserId is set? What if we run the following:

DECLARE @UserId AS INT;

SELECT @UserId = Id
FROM Users
WHERE DisplayName = 'Chad C';

Instead of throwing an error, our script…worked? Yes and no. We didn’t get an error message but we don’t know the value of @UserId yet. If we check the execution plan, we see “2 of 1” on the index seek:

SQL Server set @UserId to one value and then overwrote @UserId with a second value.

Let’s run the following and see what @UserId ends up as:

DECLARE @UserId AS INT;

SELECT @UserId = Id
FROM Users
WHERE DisplayName = 'Chad C';

SELECT @UserId;

In this case, 3082241 was the second Id found.

Who Are You?

If uniqueness of the DisplayName was guaranteed then this might not be an issue; however, if the scenario is one where you assume “the @DisplayName will always be unique” then you might be in for some confusion. We know what happens when you assume.

Thanks for reading!