CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2014
user1914202
user1914202

Reputation: 19

Creating a stored procedure in SQL Server 2014 that pulls between 2 dates

I'm a complete novice to SQL Server. I'm trying to create a stored procedure where you can search between two dates. I'd like my EXEC to read something like 

EXEC Procedure TimeSheetIndexSearch (Date, Date)

(In which case, you'd input the dates before running EXEC.

CREATE PROCEDURE TimesheetIndexSearch
AS
   SELECT 
       Timesheets.Hours AS Hours, 
       Timesheets.DateWorked AS DateWorked, 
       Timesheets.Description AS Description,
       Timesheets.Id AS Id, 
       Users.Name AS UserName, 
       Projects.Name AS ProjectName
   FROM   
       Timesheets
   INNER JOIN 
       Users ON Timesheets.UserId = Users.Id
   INNER JOIN 
       Projects ON Timesheets.ProjectId = Projects.Id;

There's only one date column (no begindate or enddate). Do I use Where? Or Order By? Thanks!

Upvotes: 0

Views: 1681

Answers (2)

Dave C
Dave C

Reputation: 7402

Here you go:

CREATE PROCEDURE TimesheetIndexSearch (@Start DATETIME, @End DATETIME)
AS
SELECT Timesheets.Hours AS Hours
       , Timesheets.DateWorked AS DateWorked
       , Timesheets.Description AS Description
       , Timesheets.Id AS Id
       , Users.Name AS UserName
       , Projects.Name AS ProjectName
FROM   Timesheets
       INNER JOIN Users ON Timesheets.UserId = Users.Id
       INNER JOIN Projects ON Timesheets.ProjectId = Projects.Id
WHERE  Timesheets.DateWorked >= @Start
       AND Timesheets.DateWorked <= @End;

You can also use the keyword between as follows:

WHERE Timesheets.DateWorked BETWEEN @Start AND @End

Just be careful with between, as it is inclusive of the dates in the variables, the first approach is cleaner, and easier to read by all.

To run the proc you'd use 

EXEC TimesheetIndexSearch '2015-01-01','2015-01-10'

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Don't create a stored procedure for this. Create a stored function. This is much more versatile because you can use it in the from clause of a select:

CREATE FRUN TimesheetIndexSearch (
    @Start date,
    @End date
)
RETURNS table
AS
    RETURN (SELECT ts.Hours AS Hours, ts.DateWorked AS DateWorked,              
                   ts.Description AS Description,
                   ts.Id AS Id, u.Name AS UserName, p.Name AS ProjectName
            FROM Timesheets ts INNER JOIN
                 Users u
                 ON ts.UserId = u.Id INNER JOIN
                 Projects p
                 ON ts.ProjectId = p.Id
            WHERE ts.DateWorked >= @Start AND ts.DateWorked <= @End
           );

You can then use this as:

select f.*
from dbo.frun('2015-01-01', '2015-02-01');

Also, note how the use of table aliases makes the query easier to write and to read.

Upvotes: 2

Related Questions