Is it possible to raise an error if assigning a variable in an element returns multiple values?

Question

I just found an error in one of my programs where I forgot a sentence somewhere. The code was something like this:

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, which I forgot, was useful in very specific cases, and the code passed the test successfully.

In the end, the request returned two values ​​instead of one, and the resulting error was a nightmare for tracking (since it was very difficult to reproduce, and it was not at all obvious that this particular stored procedure was causing the problem we noticed)

Debugging would be much simpler if @foo = foo raised an exception instead of silently assigning the first value from several lines.

? , , ( , "" "" - )

SQL Server 2008 , ?

Answer 1

:

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

Answer 2

? , , . , .

@bernd_k , , . , - ,

---

, // , - , .

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

.

Answer 3

, , :

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)
)

, .

EDIT:

, , .

Answer 4

I would assign the values ​​to the table variable and check if the table had several records after the assignment. In fact, I almost never rely on a request to return a single record, since it is shortsighted. It can be tested, but as soon as real data gets there, they often cannot, and perhaps should not even. If you count in terms of sets instead of a single record, you will have more reliable code.