Conditional column SELECT but fixed WHERE clause

• david.gray

  SSC Eights!

  Points: 968

  More actions

  September 2, 2008 at 9:46 am

  #192218

  Hi all,

  I have a select statement fired within a stored procedure which needs to return different columns depending on

  a parameter passed to the sp.

  I know I could build up the SELECT in a variable and the WHERE in a variable, append them together and run with EXEC,

  problem is the WHERE clause is rather complex and contains a lot of single quotes and I'm nervous about putting

  into a variable.

  This simple example will perhaps explain better

  create procedure test (@ExtractType )

  AS

  BEGIN

  declare @slct varchar(200)

  if @ExtractType = ‘All’

  BEGIN

  set @slct = ‘select * from country’

  END

  if @ExtractType = ‘Min’

  BEGIN

  set @slct = ‘select country_code, country from country’

  END

  exec (@slct) where country_code = 'GB'

  END

  Does anyone know if this can be achieved?

  Thanks in advance

  Dave

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  September 2, 2008 at 10:03 am

  #866071

  HI there,

  I try not to SET statements

  but this allows you to do this.

  SET QUOTED_IDENTIFIER OFF

  SELECT " tester'tester "

  Also you could look at this:

  sp_executesql

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life