SQL Query

Question

Post reply

SQL Query

ronaldkrex

Right there with Babe

Points: 738
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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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 · Answer 2

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]