Can i have multiple select but only return one result set

Question

If I have multiple selects like so:

select * from A where A.name = 'linköping'

IF @@ROWCOUNT = 0
begin
    select * from A where A.amount = 45
end

...I get 1 result set if the first select returns stuff. But if it runs the second, I get two result sets; the first with no rows and the second with some rows.

Is there a way to only return the second result set if the second select is run ?

I write code like this because of Andrey Gordeev's answer to this post: Can you have if-then-else logic in SQL?

(MSSQL 2000)

Thanks!

Answer 1

You will need to prevent the first select by checking if you will get any results back before running the select.

For example:

IF EXISTS (SELECT 1 FROM A WHERE A.name = 'linköping')
BEGIN
    SELECT * FROM A WHERE A.name = 'linköping'
END
ELSE 
BEGIN
    SELECT * FROM A WHERE A.amount = 45
END