Added new field to table and to stored procedure, now procedure returns no results

Question

I recently added a Deleted column to my table and then wanted to add an additional where clause but now I'm not getting any results anymore (it returned results before I added the deleted column)

SELECT 
    tblEquipment.*, tblUsers.* 
FROM 
    tblEquipment 
INNER JOIN 
    tblUsers ON tblEquipment.UserID = tblUsers.ID 
WHERE  
    (UPPER(tblUsers.Dept) = 'ASPIRE' OR UPPER(tblUsers.Dept) = 'DEVELOPMENT') 
    AND (AssetType = 'WORKSTATION' OR AssetType = 'LAPTOP') 
    AND (tblEquipment.Deleted != 1)  
ORDER BY 
    Username

thanks for any help

microsoft sqlserver 2008 express, i got my results until i added the deleted not equal to 1 part, currently deleted is null on all records — AlexW, May 08 '13 at 16:28

score 2 · Accepted Answer · answered May 08 '13 at 16:32

2

If Deleted is NULL for every record, your condition should be:

AND (tblEquipment.Deleted != 1 OR tblEquipment.Deleted IS NULL)

answered May 08 '13 at 16:32

Lamak

69,480
12
108
116

score 1 · Answer 2 · answered May 08 '13 at 16:29

1

I'm assuming you haven't populated your new column, so every record has NULL as the value there. Depending on how your RDMS evaluates NULL, the

tblEquipment.Deleted != 1

is probably the culprit.

UPDATE:

The following should fix your problem:

ISNULL(tblEquipment.Deleted,0) != 1

answered May 08 '13 at 16:29

Jeff B

155
7

how can I alter this so it will show the record of null and anything not 1 ? – AlexW May 08 '13 at 16:32
Updated my answer to address your question. – Jeff B May 08 '13 at 16:34
1

I would advise to not use `ISNULL` here, since it will make the use of an index on that column impossible – Lamak May 08 '13 at 16:34
@Lamak Great point there. Didn't take that into consideration. – Jeff B May 08 '13 at 16:36

Added new field to table and to stored procedure, now procedure returns no results

2 Answers2