7

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!

Community
  • 1
  • 1
Cotten
  • 8,787
  • 17
  • 61
  • 98

4 Answers4

5

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
RB.
  • 36,301
  • 12
  • 91
  • 131
  • It would be great if you replace first line with `IF EXISTS (SELECT 1 FROM A WHERE A.name = 'linkoping')` – Vishwanath Dalvi Feb 28 '13 at 09:57
  • 1
    @ViswanathanIyer Agreed. This will be a slight performance boost. I must confess that I copied-and-pasted the query from the line below :) – RB. Feb 28 '13 at 10:00
1

This will work with IF-ELSE Logic. [Tested]

DECLARE @count as int
set @count = (SELECT count(1) from A where A.name = 'linköping')
if (@count = 0)
BEGIN
    select * from A where A.amount = 45
END
ELSE
BEGIN
select * from A where A.name = 'linköping'
END
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
1

No it is not possible The returning results are depending on number of select statements. If you want to do the single select follow @RB or @Jwalin Shah solution.

Abdul Saboor
  • 4,079
  • 2
  • 33
  • 25
0

Try this

IF @@ROWCOUNT = 0
BEGIN
    select * from A where A.amount = 45
END
ELSE
BEGIN
    select * from A where A.name = 'linköping'
END
thejustv
  • 2,009
  • 26
  • 42