Return single ResultSet from Multiple

• kevin.law.pro

  Newbie

  Points: 1

  More actions

  January 20, 2010 at 7:52 pm

  #98975

  I would like to find some records out of 2 table with some criteria. If first table do not have, then would look in second table. My simple through is:

  Select * from table1 where criteria = '1'

  if @@rowcount = 0

  Select * from table2 where criteria = '1'

  However, if there is no records return from table1, there will be 2 resultset return.

  1. I want only 1 resultret to return from the store procedure.

  2. table2 is selected only with table1 do not have the record since table2 is large in volume.

  Could anyone help, Thanks in advance.

• Simon Liddle

  SSCrazy

  Points: 2580

  More actions

  January 21, 2010 at 2:11 am

  #1106549

  If you need to do this, the following should work:

  DECLARE @table1 TABLE (source VARCHAR(10), criteria VARCHAR(1))

  DECLARE @table2 TABLE (source VARCHAR(10), criteria VARCHAR(1))

  INSERT INTO @table1 (source, criteria) VALUES ('Table1', '1')

  INSERT INTO @table2 (source, criteria) VALUES ('Table2', '1')

  SELECT source, criteria FROM @table1 WHERE criteria = '1'

  UNION ALL

  SELECT source, criteria FROM @table2 WHERE criteria = '1' AND NOT EXISTS (SELECT source, criteria FROM @table1 WHERE criteria='1')

  You may want to post more info about your structure and data as there could be changes to the database that would avoid having to do it this way...

  Hope this helps 🙂