Difference between 2 stored procedures

[Kieran Dutfield]

Code:
ALTER PROCEDURE GetBookings
(
@CurrentDate datetime,
@Catergory char(10)
)
AS

SELECT BookingId, StartDate, PeriodStart, Catergory, Staff, Resource,
Class, @CurrentDate AS Expr1
FROM tblBookings
WHERE StartDate <= @CurrentDate and EndDate >= @CurrentDate and
Catergory=@Cate rgory



Code:
ALTER PROCEDURE GetBookings
(
@StartDate datetime,
@EndDate datetime,
@Catergory smallint
)
AS

SELECT *
FROM tblBookings
WHERE StartDate >= @StartDate and EndDate <= @EndDate and
Catergory=@Cate rgory

_______________ _______________ _______________ _____________
What the difference between the to stored procedures in terms what the
date filters return??


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Re: Difference between 2 stored procedures


"Kieran Dutfield" <kolo83@talk21. com> wrote in message
news:401fcc45$0 $70304$75868355 @news.frii.net. ..[color=blue]
>
> Code:
> ALTER PROCEDURE GetBookings
> (
> @CurrentDate datetime,
> @Catergory char(10)
> )
> AS
>
> SELECT BookingId, StartDate, PeriodStart, Catergory, Staff, Resource,
> Class, @CurrentDate AS Expr1
> FROM tblBookings
> WHERE StartDate <= @CurrentDate and EndDate >= @CurrentDate and
> Catergory=@Cate rgory
>
>
>
> Code:
> ALTER PROCEDURE GetBookings
> (
> @StartDate datetime,
> @EndDate datetime,
> @Catergory smallint
> )
> AS
>
> SELECT *
> FROM tblBookings
> WHERE StartDate >= @StartDate and EndDate <= @EndDate and
> Catergory=@Cate rgory
>
> _______________ _______________ _______________ _____________
> What the difference between the to stored procedures in terms what the
> date filters return??
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

There isn't enough information in your post to answer your question
properly - what is the table structure, what are the possible range of
values in each of the date columns, and what range of values might be passed
in in the parameters? What is the result set you want to see, for a given
set of sample data?

To get a good answer, you would need to post DDL for your table, sample
data, and the result set you want. But if you set up some sample data, and
simply try the two queries, I guess you'll be able to answer your own
question. Of course, if you don't understand why you get certain results,
then posting the extra information should allow someone to explain or
suggest a solution.

Simon

[Erland Sommarskog]

Re: Difference between 2 stored procedures

Kieran Dutfield (kolo83@talk21. com) writes:[color=blue]
> SELECT BookingId, StartDate, PeriodStart, Catergory, Staff, Resource,
> Class, @CurrentDate AS Expr1
> FROM tblBookings
> WHERE StartDate <= @CurrentDate and EndDate >= @CurrentDate and
> Catergory=@Cate rgory
>...
> SELECT *
> FROM tblBookings
> WHERE StartDate >= @StartDate and EndDate <= @EndDate and
> Catergory=@Cate rgory
>
> _______________ _______________ _______________ _____________
> What the difference between the to stored procedures in terms what the
> date filters return??[/color]

The first query returns all rows where the @CurrentDate falls within
the interval of [StartDate, EndDate].

The second query returns all rows where the entire interval [StartDate,
EndDate] falls within the interval of [@StartDate, @EndDate].

Open intervals, where one or both value are NULL will not be included,
and if any of the variables are NULL, no row will be returned in either
query.

If there are time portions in some of the values, this can lead to
confusion, if you expect the filtering to look at the date only.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp