COALESCE

COALESCE (Transact-SQL)
SQL - 使用 COALESCE
SQL Server：認識 COALESCE() 函數

select 
(coalesce(
 (select Z.LMT_MAXSTUD as LMT_MAXSTUD from COC_LIMITE Z
  where Z.LMT_SEMNO = B.CS_SEMNO and Z.LMT_COURSEFLNO = B.CS_COURSEFLNO
    and Z.LMT_COURSEGRADE = B.CS_COURSEGRADE and Z.LMT_COURSECLASS = B.CS_COURSECLASS)
 ,(select Z.CLR_SEAT from COS_CLASSROOM Z 
   where Z.CLR_BUILDING = (case isnull(B.CS_CLASSROOMNO, '') when '' then '' else substring(B.CS_CLASSROOMNO, 1, 1) end) 
     and Z.CLR_CODE = (case isnull(B.CS_CLASSROOMNO, '') when '' then '' else substring(B.CS_CLASSROOMNO, 2, len(B.CS_CLASSROOMNO) - 1) end))
 ,0)) as CST_MAXSTUD
from B_TEMP B