Is it possible to raise an error if a variable assignment in a select returns multiple values?
- by Brann
I just found a bug on one of my softwares where I had forgotten a where clause. The code was something like that :
declare @foo bigint
declare @bar bigint
select @foo = foo, @bar=bar from tbFooBar
where (....a long list of condition goes there)
(... and an extra condition should have went there but I forgot it)
Unfortunately, the where clause I forgot was useful in very specific corner cases and the code went through testing successfully.
Eventually, the query returned two values instead of one, and the resulting bug was a nightmare to track down (as it was very difficult to reproduce, and it wasn't obvious at all that this specific stored procedure was causing the issue we spotted)
Debugging would have been a lot easier if the @foo=foo had raised an exception instead of silently assigning the first value out of multiple rows.
Why is that this way? I can't think of a situation where one would actually want to do that without raising an error (bearing in mind the clauses 'distinct' and 'top' are there for a reason)
And is there a way to make sql server 2008 raise an error if this situation occurs ?
