SQL Server - Conditional OUTER APPLY

sql, sql-server-2008

Solution

You can readily do this with just a join:

SELECT t1.*, t2.*
FROM   t1 cross join
       (SELECT *
        FROM (SELECT top 1 tb10.*, 0 as bCcond src FROM tb10 
              UNION ALL
              SELECT top 1 tb11.*, 1 as bCcond src FROM tb11
             ) t
        WHERE @bCond = bCcond
       ) t2

Doing the `top` before the `union all` should also help the optimizer produce a better query plan, in the event that the tables are really complex joins.

Also, doing `top` without an `order by` is generally frowned upon. It can return different rows with different invocations, but the rows are not guaranteed to be random.

Problem

Is there a way in SQL Server 2008 do to something like below? Can the outer applied(joined) table to be specified based on a condition? ``` declare @bGetExtendedInfo bit set @bGetExtendedInfo = 1 declare @param nvarchar(24) set @param = 'CO-02-BBB' select t1.*, t2.ID from t1 outer apply ( case when @bGetExtendedInfo= 0 then (select 0) as ID /* dummy value */ /*really expensive query trying to avoid when extended info is not needed*/ else (select top 1 ID from tbl1 where tbl1.code = @param) end ) t2 ```

Original source