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
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!