using if exists result?

[motil]

Hello,

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

for example:

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.

[leinad31]

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

[dee-u]

Something like this?

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

[FunkyDexter]

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.