Compound WHERE clause - syntax error

I'm picking up an Incorrect Syntax error where the connector AND is used in
a compound WHERE clause where I am trying to restrict the user to a selected
Status and Date Range.

If the Status parameter comes back empty I will return ALL statuses for the
date

CREATE Procedure spr_CountStatus
@StatusID INT=Null,
@StartDate DateTime,
@EndDate DateTime

AS

SELECT Status.Status, COUNT(StatusRequest.StatusID) AS CountStatus
FROM StatusRequest INNER JOIN
Status ON StatusRequest.StatusID = Status.StatusID
WHERE ( (StatusRequest.StatusID = @StatusID) or (@StatusID=Null)) And
(CreateDate between (@StartDate and @EndDate))
GROUP BY Status.Status
GO

where
order by CreateDate DESC
return

....There is probably a better way of writing this. I read on www.aspfaq.com
that using BETWEEN is problematic...

Any feedback welcome.
Jason

Re: Compound WHERE clause - syntax error

O damn - sorry, I pasted some extraneous code at the bottom......it should
read like this:

CREATE Procedure spr_CountStatus
@StatusID INT=Null,
@StartDate DateTime,
@EndDate DateTime

AS

SELECT Status.Status, COUNT(StatusRequest.StatusID) AS CountStatus
FROM StatusRequest INNER JOIN
Status ON StatusRequest.StatusID = Status.StatusID
WHERE (StatusRequest.StatusID = @StatusID or @StatusID=Null) And
CreateDate between (@StartDate and @EndDate)
GROUP BY Status.Status
GO

return

wrote in message
news:Onpo5U8vFHA.2132@TK2MSFTNGP15.phx.gbl...
> I'm picking up an Incorrect Syntax error where the connector AND is used
> in a compound WHERE clause where I am trying to restrict the user to a
> selected Status and Date Range.
>
> If the Status parameter comes back empty I will return ALL statuses for
> the date
>
> CREATE Procedure spr_CountStatus
> @StatusID INT=Null,
> @StartDate DateTime,
> @EndDate DateTime
>
> AS
>
> SELECT Status.Status, COUNT(StatusRequest.StatusID) AS CountStatus
> FROM StatusRequest INNER JOIN
> Status ON StatusRequest.StatusID = Status.StatusID
> WHERE ( (StatusRequest.StatusID = @StatusID) or (@StatusID=Null)) And
> (CreateDate between (@StartDate and @EndDate))
> GROUP BY Status.Status
> GO
>
> where
> order by CreateDate DESC
> return
>
> ...There is probably a better way of writing this. I read on
> www.aspfaq.com that using BETWEEN is problematic...
>
> Any feedback welcome.
> Jason
>

Re: Compound WHERE clause - syntax error

jason@catamaranco.com wrote:
> O damn - sorry, I pasted some extraneous code at the bottom......it
> should read like this:
>
> CREATE Procedure spr_CountStatus
> @StatusID INT=Null,
> @StartDate DateTime,
> @EndDate DateTime
>
> AS
>
> SELECT Status.Status, COUNT(StatusRequest.StatusID) AS CountStatus
> FROM StatusRequest INNER JOIN
> Status ON StatusRequest.StatusID = Status.StatusID
> WHERE (StatusRequest.StatusID = @StatusID or @StatusID=Null) And
> > GROUP BY Status.Status
> GO
>
This should work
CreateDate between @StartDate and @EndDate


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Compound WHERE clause - syntax error

Perfect: Bob, one last thing: If I wanted to extend this to allow Dates to
Handle Nulls how would the where clause be written...I have tried this, but
picking up syntax errors at the first OR...


CREATE Procedure spr_CountStatus
@StatusID INT=Null,
@StartDate DateTime=Null,
@EndDate DateTime=Null

'//etc....

WHERE (StatusRequest.StatusID=@StatusID or @StatusID=Null) And CreateDate
between (@StartDate or @StartDate=Null) and (@EndDate or @EndDate=Null)

'//etc

Re: Compound WHERE clause - syntax error

jason@catamaranco.com wrote:
> Perfect: Bob, one last thing: If I wanted to extend this to allow
> Dates to Handle Nulls how would the where clause be written...I have
> tried this, but picking up syntax errors at the first OR...
>
>
> CREATE Procedure spr_CountStatus
> @StatusID INT=Null,
> @StartDate DateTime=Null,
> @EndDate DateTime=Null
>
> '//etc....
>
> WHERE (StatusRequest.StatusID=@StatusID or @StatusID=Null) And
> CreateDate between (@StartDate or @StartDate=Null) and (@EndDate or
> @EndDate=Null)
> '//etc

Nothing ever equals Null. Check for nullness by using IS instead of =.

WHERE (StatusRequest.StatusID=@StatusID or @StatusID=Null) And
(CreateDate between @StartDate AND @EndDate OR
@StartDate IS Null OR @EndDate IS Null)

Alternatively:

WHERE (StatusRequest.StatusID=@StatusID or @StatusID=Null) And
CreateDate between COALESCE(@StartDate,19000101') AND
COALESCE(@EndDate,'30001231')

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Compound WHERE clause - syntax error

Got You - by why is it acceptable to use =Null in the Parameters
clause.......?

"Bob Barrows [MVP]" wrote in message
news:%2330s$MrwFHA.2076@TK2MSFTNGP14.phx.gbl...
> jason@catamaranco.com wrote:
>> Perfect: Bob, one last thing: If I wanted to extend this to allow
>> Dates to Handle Nulls how would the where clause be written...I have
>> tried this, but picking up syntax errors at the first OR...
>>
>>
>> CREATE Procedure spr_CountStatus
>> @StatusID INT=Null,
>> @StartDate DateTime=Null,
>> @EndDate DateTime=Null
>>
>> '//etc....
>>
>> WHERE (StatusRequest.StatusID=@StatusID or @StatusID=Null) And
>> CreateDate between (@StartDate or @StartDate=Null) and (@EndDate or
>> @EndDate=Null)
>> '//etc
>
> Nothing ever equals Null. Check for nullness by using IS instead of =.
>
> WHERE (StatusRequest.StatusID=@StatusID or @StatusID=Null) And
> (CreateDate between @StartDate AND @EndDate OR
> @StartDate IS Null OR @EndDate IS Null)
>
> Alternatively:
>
> WHERE (StatusRequest.StatusID=@StatusID or @StatusID=Null) And
> CreateDate between COALESCE(@StartDate,19000101') AND
> COALESCE(@EndDate,'30001231')
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Compound WHERE clause - syntax error

jason@catamaranco.com wrote:
> Got You - by why is it acceptable to use =Null in the Parameters
> clause.......?
>
Assigning Null to a variable/parameter/object is much different than testing
to see if that variable/parameter/object contains Null. Any comparison or
arithmetic operation involving Null will result in Null. With
vb/vba/vbscript, a boolean expression that returns Null is equivalent to
false. The same applies to T-SQL. Try this:

<%
If Null = Null then
response.write "Null equals Null!"
else
response.write "vbscript cannot tell if one unknown value equals another
unknown value"
end if
%>

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"