Query

• joemai

  Hall of Fame

  Points: 3496

  More actions

  August 23, 2005 at 4:34 pm

  #102729

  I have input parameter store procedure.  Depending on the  parameter passed in, my query have to change the field on it.  My query is:

  If @offer = 'USA'

  Begin

                 select top 5* from mytable1 where status = 'A'

  Else

                Select top 5 * from mytable1 where status_country = 'A'

  End

   

  Can this be done in one query instead of 2 queries like above. How can I instruct my query to change the field name depending on the parametter?

  This will be a great help for me.

  Thank so much.

  Minh Vu

• wz700

  SSCertifiable

  Points: 7133

  More actions

  August 23, 2005 at 6:23 pm

  #584435

  One option is using dynamic sql:

  If @offer = 'USA'

  declare @where varchar(1000)

  Begin

     set @where =    'status = ''A'''

  Else

     set @where = 'status_country = ''A'''

  End

  Exec('select top 5 * from mytable1 where '+@where)

• Murthy-152277

  Right there with Babe

  Points: 720

  More actions

  August 23, 2005 at 11:00 pm

  #584471

  Hi,

  u can do this in a single query using case statement.

   

  select top 5* from mytable1 where CASE when @offer = 'USA' THEN status

  ELSE status_country END  = 'A'

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 24, 2005 at 7:13 am

  #584569

  There's just no need for dynamic sql here ::

  The Curse and Blessings of Dynamic SQL

  Dynamic Search Conditions in T-SQL

  Arrays and Lists in SQL Server