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

Question

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 ?

Answer 1

Try this:

declare @d datetime
select @d = arrived from attendance;
if @@ROWCOUNT > 1 begin
    RAISERROR('Was more than expected 1 row.', 16, 1)
end

Answer 2

Why is that this way? People can do quite a lot based on the fact the variable is assigned on each row. For instance, some use it to perform string concatenation.

@bernd_k demonstrates one way to cause an error, assuming that you're only assigning to a single variable. At the moment, there's no way to generalise that technique if you need to assign multiple variables - you still need to ensure that your query only returns one row

---

If you're concerned that a particular query is large/complex/might be edited later, and somebody might accidentally cause it to return additional rows, you can introduce a new variable, and make the start of your select look like this:

declare @dummy bit
select @dummy = CASE WHEN @dummy is null then 1 ELSE 10/0 END

This will then cause an error if multiple rows are returned.