Geek Answers Handbook

SQL Server - OUTER APPLY Conditional Application

Is there a way in SQL Server 2008 to do something like below?

Is it possible to specify an external application (join) table 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
+4
source share
2 answers

You can easily do this only with a connection:

 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 top before union all should also help the optimizer create a better query plan if the tables are really complex.

In addition, executing top without order by usually not approved. It may return different rows with different calls, but rows are not guaranteed to be random.

+4
source

Try

 DECLARE @bCond BIT SET @bCond = 1 SELECT t1.*, t2.* FROM t1 OUTER APPLY ( SELECT TOP 1 * FROM ( SELECT *, 'a' src FROM tb10 UNION ALL SELECT *, 'b' src FROM tb11 )s WHERE src = CASE WHEN @bCond = 0 THEN 'a' ELSE 'b' END ) t2
+4
source

More articles:


All Articles