SQL Query

• ronaldkrex

  Right there with Babe

  Points: 768

  More actions

  March 31, 2010 at 2:43 pm

  #225719

  Could someone look at the query below and tell me why it would produce the results that I included at the bottom. I entered the value 'Rex' for LastName. I would have thought that only the results that have a Lastname as Rex and a CardOrKey=Key should be returned

  SELECT * FROM [dbo].[tblReportData]

  WHERE CardORKey='Key' or CardORKey='Both'

  AND (LastName = @LastName OR

  @LastName IS NULL)

  AND (Date >= @StartDate OR @StartDate

  IS NULL)

  AND (Date < dateadd(dd,1,@EndDate) OR

  @EndDate IS NULL)

  AND( Department = @Department OR

  @Department IS NULL)

  LastName CardORKey

  Rex Key

  Rex Key

  chronister Key

  Gandy Key

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  March 31, 2010 at 2:56 pm

  #1143498

  It is because of your WHERE condition with the OR statement. Group the OR condition together like this:

  WHERE(CardORKey='Key' or CardORKey='Both')

  AND (LastName = @LastName OR @LastName IS NULL)

  AND (Date >= @StartDate OR @StartDate IS NULL)

  AND (Date < dateadd(dd,1,@EndDate) OR @EndDate IS NULL)

  AND( Department = @Department OR @Department IS NULL)

  John Rowan

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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  March 31, 2010 at 2:56 pm

  #1143499

  Your WHERE condition is transformed into:

  WHERE

  (CardORKey='Key')

  OR

  (

  CardORKey='Both'

  AND

  ( LastName = @LastName

  OR

  @LastName IS NULL

  )

  AND ...

  )

  Edit: Follow Johns advice.

  Additionally, please have a look at Gails blog[/url] regarding catch-all queries.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]