in a select clause, include a column if it exists, or just return null if not

• mitz 39799

  Valued Member

  Points: 56

  More actions

  February 16, 2011 at 9:46 am

  #235139

  presuming i have table abc (col1 varchar) on some production servers and i have table abc (col1 varchar, col2 xml) on others.

  is there any way to select the 2nd column only if it exists?

  The ideal way would be :

  select col1, (case when @colExists=1 then (select top 1 col2 from abc a2 where a1.col1=a2.col2) else null end ) as blah

  from abc a1

  but this throws an error at runtime.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  February 16, 2011 at 10:00 am

  #1287776

  You'd have to use dynamic SQL for that.

  Build the query string using sys.columns, then execute it using sp_executesql.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon