CODEWITHSUNDEEP
sqlsql-server
multipack1
multipack1

Reputation: 41

Stored procedure not returning expected values

I'm executing the following stored procedure which returns 0 values:

CREATE PROCEDURE [dbo].[sproc_tblBook_DisplayReservations]

@bor_id varchar (9)

AS
    SELECT bor_id, isbn, bk_author, bk_title, 
            cop_loan_type, cop_status, res_date

    FROM tblBook, tblReservation, tblCopy, tblBorrower

    WHERE
    tblReservation.res_cleared_date = NULL
    AND
    tblBook.isbn = tblReservation.fk1_isbn
    AND
    tblBook.isbn = tblCopy.fk1_isbn
    AND
    tblBorrower.bor_id = tblReservation.fk2_bor_id
    AND
    bor_id = @bor_id;
RETURN 0

The desired result, of course, is that it shows records which match the where conditions' criteria.
Such records exist, yet they're not showing.
I'm sure my syntax's correct, so I'm completely and totally stumped.
At a glance, if you've suggestions, please tell.
I've linked my program here should anyone want a closer look.
Unfortunately, I think that's pretty much paramount to assisting me with this issue, but thank you at all should anyone read this.
The following link is my program which I'm working on in Visual Studio (.zip file -- feel free to upgrade the .mdf file/sql server if necessary): http://s000.tinyupload.com/index.php?file_id=07002279584481194620

Please, if you have time, download my program and see if you can find the issue!

Upvotes: 0

Views: 105

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82534

Change tblReservation.res_cleared_date = NULL to tblReservation.res_cleared_date IS NULL. In Sql, nothing equals null, not even another null.
Also, change the implicit join to explicit join:

CREATE PROCEDURE [dbo].[sproc_tblBook_DisplayReservations]

@bor_id varchar (9)

AS
    SELECT  bor_id, isbn, bk_author, bk_title, 
            cop_loan_type, cop_status, res_date
    FROM tblBook
    INNER JOIN tblReservation ON tblBook.isbn = tblReservation.fk1_isbn 
    INNER JOIN tblCopy ON tblBook.isbn = tblCopy.fk1_isbn
    INNER JOIN tblBorrower ON tblBorrower.bor_id = tblReservation.fk2_bor_id
    WHERE tblReservation.res_cleared_date IS NULL
    AND bor_id = @bor_id;
RETURN 0

Upvotes: 2

Related Questions