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

I just found a bug on one of my softwares where I had forgotten a where clause. T开发者_运维知识库he 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 ?

Try this:

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

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.

You can formulate your query like this and than you get errors, when there are multiple results:

declare @foo bigint
select @foo = (
    Select foo 
    from tbFoo
    where (....a long list of condition goes there)
         (... and an extra condition should have went there but I forgot it)
)

The other syntax is designed not to throw errors.

EDIT:

When you need more than 1 column, you can use a table variable, assign the result to it and check its row count and work accordingly.

I would assign the values to a table variable and check to see if the table had multiple records after assignment. In fact I almost never rely on a query to return one record as that is short-sighted. It may in testing but once real data gets there they often do not and maybe even should not. If you think in terms of sets instead of one record, you will have more reliable code.