Is it possible to raise an error if a variable assignment in a select returns multiple values?

Posted by Brann on Stack Overflow See other posts from Stack Overflow or by Brann
Published on 2010-12-31T08:55:16Z Indexed on 2010/12/31 15:54 UTC
Read the original article Hit count: 236

Filed under:
|
|

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 ?

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql