Does that title make any sense to you?
If not, can you bear with me? I’ll make it worth your while, I promise!
I’ve been looking at a bit of code that threw an error for the first time in nearly a year. This ‘bit’ of code is present on four different environments, and is run under some particularly strenuous and varied conditions.
Here is the gist of the code:
SELECT @var = ISNULL((SELECT col1 FROM SomeTable WHERE col2='SomeValue'), 0)
Those gurus (and some lesser mortals) among us will spot the potential problem immediately. For those, this sort of issue sticks out like sore thumb. But, for the rest of us it isn’t so inherently obvious.
Let me demonstrate. Imagine our ‘SomeTable’ has the following data:
We are assuming that statement we are passing to our ISNULL function will only ever return one result. However, in the above example it does not. You can try it yourself; simply paste the SQL below into your query window:
create table #temp (col1 int, col2 varchar(10)); insert into #temp values (1,'SomeValue'); insert into #temp values (2,'SomeValue'); select isnull((select col1 from #temp where col2 = 'SomeValue'),0) drop table #temp
You should get the following message:
Msg 512, Level 16, State 1, Line 4 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Understanding the data is key to resolving this. You have assumed there will only be one value, so what are you prepared to accept? Possible solutions include:
select isnull((select max(col1) from #temp where col2 = 'SomeValue'),0) select isnull((select min(col1) from #temp where col2 = 'SomeValue'),0) select isnull((select top 1 col1 from #temp where col2 = 'SomeValue'),0) select isnull((select distinct col1 from #temp where col2 = 'SomeValue'),0)
The first three solve the problem for you in all cases. In the fourth example, the use of the distinct clause will only work if your result set is identical. In fact, it will still break for our example.
But, by putting one of these fixes in place, we are maybe missing the whole point. Although defensive programming can help trap this sort of error, we are overlooking the fact that we have maybe stumbled upon some sort of data integrity error. I.e. do we have two records where we really shouldn’t?
Then the fix is something more fundamental, for example, are we missing a unique constraint somewhere? Here is our table definition with the constraint:
create table #temp (col1 int, col2 varchar(10), constraint UK_COL2 unique (COL2));
Running our original code block with this in place shows up the error when we try to insert a duplicate value in Col2:
Msg 2627, Level 14, State 1, Line 3 Violation of UNIQUE KEY constraint 'UK_COL2'. Cannot insert duplicate key in object 'dbo.#temp'.
Sure, we still get the error, however, we get it earlier in our process, which is generally better. The is because we may not query these data for some time after it has been inserted, so it may be some time before the original error presented itself. In addition to this, the chances are that if you’ve coded a unique constraint then you’ll be more inclined to test for it.
So, a few things to think about. Principally, what do you want your subquery to return, and is your data correct?