CODEWITHSUNDEEP
sqlsql-serverparametersprocedure
Glen
Glen

Reputation: 21

Stored procedure - Date

In process of a college project that requires a simple stored procedure :

Create a stored procedure that takes a single parameter that accepts a Date. Data type of Date will be datetime. This store procedure should return all renter and property information for viewings that occurred on that date.

It will be submitted on SQL Server from college, but at the moment at home I'm training with MS Access 2010.

Table = Property_For_Rent:

Pno  \\( this is the property ID )
Address_1
Address_2
Address_3

Table = Renter

Rno \\( this is the renter ID )
Fname
Lname

Table = Viewing 

Rno
Pno
Date

Researching found another fellow on here with similar conundrum ( easier to tweak, if anyone can shine where went wrong be great , thank you! 

create procedure sp_orders_by_dates
    @startdate smalldatetime,
    @enddate smalldatetime
as
    select  
       OrderID,
       o.CustomerID,
       c.CompanyName as CustomerCompany,
       s.ShipperID,
       s.CompanyName as ShipperCompany,
       ShippedDate
    from    
       Orders o 
    join 
       Customers c on o.CustomerID = c.CustomerID 
    join 
       Shippers s on s.ShipperID = o.ShipperID
    where 
       @startdate = ShippedDate,
       @enddate = ShippedDate
    order by 
       ShippedDate

Upvotes: 2

Views: 272

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20794

Since you are working with a datetime datatype, you have to factor in the time of day.

 where shippedDate >= @dateParameter
 and shippedDate < DateAdd(day, 1, @dateParameter)

Get used to this approach when working with datatypes that include both a date and a time.

Upvotes: 0

Mike Robinson
Mike Robinson

Reputation: 8945

SQL implementations aren't the same. They don't all use exactly the same syntax, and they don't all implement exactly the same things. For instance, MS-Access (so far as I know) doesn't provide stored-procs at all. (But it does know how to issue queries and DML-commands, to appropriate servers that do.)

Such as, for instance, "SQL Server!" :-) Microsoft does provide, free of charge, "SQL Server Express" which is (most of ...) SQL Server, designed for use on one machine. Using this tool, you should be able to replicate virtually the same environment that you expect to be using in the lab. And of course, MS-Access is a very fine tool to use to, well, "access" what you've done.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

I don't get it.
The tables you described are not even mentioned on the stored procedure.
How do you expect it to work?

You should use INNER JOIN on all 3 tables, and compare the Date column on table Viewing to your @Date variable in the WHERE clause.
Basically, it should look something like this:

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2 ON(T1.JoinColumn = T2.JoinColumn)
INNER JOIN Table3 T3 ON(T2.OtherJoinColumn = T3.JoinColumn)
WHERE Table3.ConditionColumn = @Date

Now all you have to do is figure out how to fit your tables and columns into that sql statement instead of my mockups.
Good luck.

Upvotes: 1

Related Questions