Variable To Select All

• Richard Sabbara

  Ten Centuries

  Points: 1382

  More actions

  July 21, 2006 at 12:47 pm

  #90342

  I have the following code:

  Declare @market as varchar (30)

  SET @market = 'Test'

  Select *

  from VarianceReportTable

  where Market = @market

   

  This works excellent, I know however that there is a way to pass a -1 in for all since there are several markets and this is a small of an SP that I am trying to set up where they need to be able to pass in either the name of the market or -1 for all, I am just drawing a blank on they syntax.  Any help is appreciated.

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  July 21, 2006 at 1:04 pm

  #650841

  DECLARE @market as varchar(30)

  SET @market = 'Test'

  IF @market = '-1'

      SELECT * FROM VarianceReportTable

  ELSE

      SELECT * FROM VarianceReportTable

      WHERE Market = @market

   

  Or you could use a case statement,

   

  DECLARE @market as varchar(30)

  SET @market = 'Test'

  SELECT * FROM VarianceReportTable

  WHERE Market = CASE @market WHEN '-1' THEN Market ELSE @market END

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  July 21, 2006 at 2:18 pm

  #650867

  I prefer an OR statement:

  SELECT * FROM VarianceReportTable

  WHERE Market = @market

  OR @market = '-1'