HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.programming » Simple Stored Procedure using Date Parameters

Topic: RE: Simple Stored Procedure using Date Parameters

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 10:33:00 AM

By: Anonymous

sounds like homework to me.

Why don't you share what you've tried so far?

"robboll" wrote:

> SQL Server 2005
>
> Table Name:
> Students
>
> Fields:
> FName, LName, DateEnrolled
>
> stored procedure name: sp_test
>
>
> What would a stored procedure syntax be to query the table using two
> parameters StartDate and EndDate. So if I wanted the sp to return
> everyone who enrolled in (e.g., May 2008).
>
> Sorry this is so simplestic. I was finding too many examples so I
> thought I'd keep it simple.
>
> RBolling
>

Replies below ↓

Replies

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 10:37:53 AM

By: Plamen Ratchev

Re: Simple Stored Procedure using Date Parameters

The "sp" prefix is used for system stored procedures, so you should not use
it for user stored procedures.

Here is what the stored procedure may look like:

CREATE PROCEDURE GetEnrolledStudents
@StartDate DATETIME,
@EndDate DATETIME
AS
SELECT Fname, LName, DateEnrolled
FROM Students
WHERE DateEnrolled BETWEEN @StartDate AND @EndDate;

Then execute as follows:

EXEC GetEnrolledStudents '20080501', '20080531';

Note it is using BETWEEN which is inclusive of the boundary dates. Also,
assumes DateEnrolled has time portion set at midnight. If not the case then
you can change to:

CREATE PROCEDURE GetEnrolledStudents
@StartDate DATETIME,
@EndDate DATETIME
AS
SELECT Fname, LName, DateEnrolled
FROM Students
WHERE DateEnrolled >= @StartDate
AND DateEnrolled < DATEADD(DAY, 1, @EndDate);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 1:34:45 PM

By: robboll

Simple Stored Procedure using Date Parameters

SQL Server 2005

Table Name:
Students

Fields:
FName, LName, DateEnrolled

stored procedure name: sp_test

What would a stored procedure syntax be to query the table using two
parameters StartDate and EndDate. So if I wanted the sp to return
everyone who enrolled in (e.g., May 2008).

Sorry this is so simplestic. I was finding too many examples so I
thought I'd keep it simple.

RBolling

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 1:34:49 PM

By: robboll

Re: Simple Stored Procedure using Date Parameters

On Jun 10, 11:37=A0am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> The "sp" prefix is used for system stored procedures, so you should not us=
e
> it for user stored procedures.
>
> Here is what the stored procedure may look like:
>
> CREATE PROCEDURE GetEnrolledStudents
> =A0@StartDate DATETIME,
> =A0@EndDate DATETIME
> AS
> =A0SELECT Fname, LName, DateEnrolled
> =A0FROM Students
> =A0WHERE DateEnrolled BETWEEN @StartDate AND @EndDate;
>
> Then execute as follows:
>
> EXEC GetEnrolledStudents '20080501', '20080531';
>
> Note it is using BETWEEN which is inclusive of the boundary dates. Also,
> assumes DateEnrolled has time portion set at midnight. If not the case the=
n
> you can change to:
>
> CREATE PROCEDURE GetEnrolledStudents
> =A0@StartDate DATETIME,
> =A0@EndDate DATETIME
> AS
> =A0SELECT Fname, LName, DateEnrolled
> =A0FROM Students
> =A0WHERE DateEnrolled >=3D @StartDate
> =A0 =A0 =A0AND DateEnrolled < DATEADD(DAY, 1, @EndDate);
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thanks! This should give me an A+! jk

Page 1 of 1 Pages