Difference between 2 stored procedures

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=@.Catergory

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

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

__________________________________________________ ________
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!"Kieran Dutfield" <kolo83@.talk21.com> wrote in message
news:401fcc45$0$70304$75868355@.news.frii.net...
> 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=@.Catergory
>
> Code:
> ALTER PROCEDURE GetBookings
> (
> @.StartDate datetime,
> @.EndDate datetime,
> @.Catergory smallint
> )
> AS
> SELECT *
> FROM tblBookings
> WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
> Catergory=@.Catergory
> __________________________________________________ ________
> 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!

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|||Kieran Dutfield (kolo83@.talk21.com) writes:
> SELECT BookingId, StartDate, PeriodStart, Catergory, Staff, Resource,
> Class, @.CurrentDate AS Expr1
> FROM tblBookings
> WHERE StartDate <= @.CurrentDate and EndDate >= @.CurrentDate and
> Catergory=@.Catergory
>...
> SELECT *
> FROM tblBookings
> WHERE StartDate >= @.StartDate and EndDate <= @.EndDate and
> Catergory=@.Catergory
> __________________________________________________ ________
> What the difference between the to stored procedures in terms what the
> date filters return??

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/techin.../2000/books.asp

difference among them

Hi everyone,
What is the real difference among them ?
CREATE PROCEDURE sp_devamsizlik
@.ADI CHAR(20)
AS
SELECT *
FROM OGRENCI
WHERE ADI=@.ADI

GO
--

CREATE FUNCTION sp_devamsizlik
(@.ADI

CHAR(20) )
RETURN TABLE
BEGIN
RETURN( SELECT * FROM OGRENCI WHERE ADI=@.ADI )
END

The short answer could be something like; they are both tools in your toolbox, one is a hammer and the other is a screwdriver. They are used to build with, but at different times for different purposes. Which is best depends on if you're looking at a nail or a screw.

The 'real' difference could fill many many pages, so I'd like to point you to BOL first. There's a lot in there that explains about procedures and functions. The one thing that may not be so obvious in BOL, is that functions aren't always the magic wand that one could be led to believe. UDF's should be used with caution, but used right, they can serve you very well.

/Kenneth

|||

Hello

The First one is Storde Procedure and the Second one is User Define function.

Search for Diffrence between SP and UF in detail

- You can'nt call a UF in SP while in SP u can
-In a Query u can call UF but u Can'nt Call SP
-

|||

Akbar Khan wrote:

- You can'nt call a UF in SP while in SP u can
-In a Query u can call UF but u Can'nt Call SP
-

Hi, would you mind being more clear ?|||

Hi

Sorry there is an error in the posted syntex: actually

In Stored Procedure you can call a User Define Function Like If write the follwing query in Stored Procedure then it will run with out any error.
Suppose we have a GetName User Define Function which returns Student Name and accept the Student Id as a aparameter.

Select dbo.GetName(std_id) FROM STUDENT

Here you can also see that a User Define function is called in SQL Select Query.

But you cann't call the follwing syentex in User Define function

Exec SP_DoSomething

SP_DoSomeThing is a stored procedure.

The Vest way to Understand Code it....