Return same select if exists-CodePudding

Having a query like:

IF EXISTS(SELECT * FROM [MyTable] WHERE Property=1)
    BEGIN
        SELECT * FROM [MyTable] WHERE Property=1
    END
else
    begin
        SELECT * FROM [MyTable] WHERE Property= (SELECT Property FROM [OtherTable] where OtherProperty = 1)
    end

Is it possible to modify it in order to not do the same select twice?

CodePudding user response：

Try this,

DECLARE @Property INT= 1;
DECLARE @LocalProperty INT;
IF EXISTS
(
    SELECT 1
    FROM [MyTable]
    WHERE Property = @Property
)
    BEGIN
        SET @LocalProperty = @Property;
    END;
    ELSE
    BEGIN
        SELECT @LocalProperty = Property
        FROM [OtherTable]
        WHERE OtherProperty = @Property;
    END;
SELECT *
FROM [MyTable]
WHERE Property = @LocalProperty;

CodePudding user response：

You can use Merge Concatenation to do this. Just join the two queries with UNION ALL, add an ordering column to ORDER BY, then SELECT TOP (1) WITH TIES

SELECT TOP (1) WITH TIES`
  *
FROM (
    SELECT *, 1 AS Ordering
    FROM [MyTable] t
    WHERE Property = 1

    UNION ALL

    SELECT *, 2
    FROM [MyTable]
    WHERE Property = (
        SELECT Property
        FROM [OtherTable]
        where OtherProperty = 1)
) t
ORDER BY Ordering;

If Property in the second query is guaranteed to be >= 1 then you can just do it all in one and ORDER BY Property

SELECT TOP (1) WITH TIES`
  *
FROM [MyTable] t
WHERE Property IN (
 1,
 (
     SELECT Property
     FROM [OtherTable]
     where OtherProperty = 1
  )
ORDER BY Property;

I suggest you have an index on Property with relevant INCLUDEs to support this query