Select statement with logical operator

• ahmadjk

  SSC Journeyman

  Points: 82

  More actions

  January 11, 2010 at 8:27 am

  #1101835

  yes I could restrict it by entering any or all parameters

• ahmadjk

  SSC Journeyman

  Points: 82

  More actions

  January 11, 2010 at 8:44 am

  #1101852

  i mean i could restrict it by @param.

  Could you think of SQL construct. How should be query be ?

  Thnaks for your help

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 11, 2010 at 8:47 am

  #1101858

  Try this:

  -- parameters

  DECLARE @Year CHAR(4), @Month VARCHAR(10)

  SELECT @Year = '2008', @Month = 'February'

  -- variable

  DECLARE @RowsToReturn INT

  -- test rowcount using user-supplied values; ignore parameter if NULL

  SELECT @RowsToReturn = COUNT(*)

  FROM #test t

  WHERE t.[year] = ISNULL(@Year, t.[year])

  AND t.[month] = ISNULL(@Month, t.[month])

  IF @RowsToReturn = 0

  SELECT *

  FROM #test t

  ELSE

  SELECT *

  FROM #test t

  WHERE t.[year] = ISNULL(@Year, t.[year])

  AND t.[month] = ISNULL(@Month, t.[month])

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ahmadjk

  SSC Journeyman

  Points: 82

  More actions

  January 11, 2010 at 9:11 am

  #1101875

  Great !!! Its working

  Thanks for your help.