sql - Select with subquery avoid running more than once? -

i have query this

select       col_a     ,col_b     ,col_c     ,case          when (select ...{a complicated query}...) not null 1         else 0      end case col_d     mytable 

the subquery produces col_d returns integer when not null.

i need show bit col_d is, need show int returns. normally, rewrite subquery , call col_e, given it's complexity, don't want run twice. ideally, have:

select      col_a     ,col_b     ,col_c     ,(select ...{a complicated query}...) col_e     ,case          when col_e not null 1         else 0      end case col_d     mytable 

do have options? (ms sql 2008)

edit: sorry - should have mentioned complicated query includes clause based on columns i.e.

select ...{a complicated query}... x = col_a , y = col_b 

so if results of query single value or null, use:

select       col_a     ,col_b     ,col_c     ,case          when t.whatevercol not null 1         else 0      end case col_d,     t.whatevercol     mytable, (select ...{a complicated query}...) t 

do careful result in cartesian product though if there potential multiple returns. if there fields can join on, might better approach (and since you're checking null, may need outer join).