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.


SET @UserId = (
		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';

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:


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:


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


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!

One thought on “Gotcha When Setting Variable”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s