using if exists result?

Hello,

Is there a way to use the if exists select statement result ?

for example:

Code:

---

IF EXISTS (SELECT col1 FROM table1 where id = 1)
  BEGIN
      SELECT col1 FROM table1 where id = 1
  END
  ELSE
  BEGIN
  -- select from other table
  END

---

in my example above I had to select the same data twice, one for checking if the data exists and then select it again to use it..

so is there a way to use the data i've already selected in the "IF EXISTS" clause ?

thanks and best regards.

Just iterate through result of query, if iteration count is zero then there were no records.

Something like this?

Code:

---

SELECT PatientNum FROM tabPatientRecords WHERE PatientNum  = 'X'
IF @@ROWCOUNT  = 0
        SELECT PatientNum FROM tabPatientRecords WHERE PatientNum  = 'PN1'

---

Haven't actually tried this and it's a bit on the cludgy side but I think you could use an ISNULL and Sub Selects:-

SELECT ISNULL(
(SELECT col1 FROM table1 where id = 1),
(SELECT col1 FROM table2 where id = 1))


if the record doesn't exist in table 1 the first subselect will return a null so the isnull will cause the second select to be run instead.