SQL Server Query with boolean issue

Question

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[TCCPAUsersAndNamesByJobPosition] @EmpOrfreeLance bit

AS
BEGIN
    SET NOCOUNT ON;

    SELECT distinct t1.UserId , tblCustomers.name 
    FROM tblTime t1
    inner join tblCustomers on t1.UserId=tblCustomers.custID
    where (t1.userid in (select distinct custID from tblCustomers where sectionCat Like '%,35%') )
    AND (t1.UserId in (select distinct custID from tblCustomers where IsEmployee = @EmpOrfreeLance))

end

tried it also with IsEmployee = CONVERT(bit,@EmpOrfreeLance)

and SET @EmpOrfreeLance= CASE @EmpOrfreeLance WHEN 1 THEN 0 ELSE 0 END

same all it retuns same list with same results no matter what

Shouldn't it be simple ???

IsEmployee col-datatype is (bit,null)

my dev SQL server is 2008 ..online server is 2005 should it be a matter ...

You can remove the distinct keyword for the select's inside in IN as it will do nothing. — Magnus
– Magnus, Commented Oct 29, 2012 at 22:05
@Magnus thanks , done , but i shall leave it as it is here with your comment as an example to somthing that has not effect . — LoneXcoder
– LoneXcoder, Commented Oct 29, 2012 at 22:23

jtimperley · Accepted Answer · 2012-10-29 22:08:52Z

2

Comparing null values will always return false. You've stated that IsEmployee can be null which is probably your case.

1 == NULL => False
0 == NULL => False
NULL == NULL => False

Try using something like this for your comparison:

(@EmpOrfreeLance IS NULL
    OR IsEmployee IS NULL
    OR IsEmployee = @EmpOrfreeLance)

Or

ISNULL(IsEmployee, 0) = ISNULL(@EmpOrfreeLance, 0)

answered Oct 29, 2012 at 22:08

jtimperley

2,55414 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jtimperley Over a year ago

I should have noted that this is how SQL Server allows you to control this with the ANSI_Nulls option on your database and/or query.

LoneXcoder Over a year ago

what are those actually , i totally neglected them :SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

Magnus · Accepted Answer · 2012-10-29 22:25:39Z

2

I might be missing something, but why dont you write the query like this?

SELECT distinct t1.UserId, tblCustomers.name 
FROM tblTime t1
inner join tblCustomers on t1.UserId=tblCustomers.custID
where sectionCat Like '%,35%' AND 
      ISNULL(IsEmployee, CAST(0 As bit)) = @EmpOrfreeLance

Also you are going to have to decide what to do when IsEmployee is null. (is it an Employee or not) For example by using ISNULL(IsEmployee, CAST(0 As bit)) to treat NULL values a false.

edited Oct 29, 2012 at 22:25

answered Oct 29, 2012 at 22:16

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

Comments

LoneXcoder · Accepted Answer · 2012-10-29 22:26:39Z

0

i was wrong caus i missed a thing

    SELECT distinct t1.UserId , tblCustomers.name 
    FROM tblTime t1
    inner join tblCustomers on t1.UserId=tblCustomers.custID
    where (t1.userid in (select distinct custID from tblCustomers where sectionCat Like '%,35%') )
    AND (t1.UserId in (select distinct custID from tblCustomers where IsEmployee = @EmpOrfreeLance))
AND (isActive = 1 AND isActiveAgent  = 1 AND sivug Like '%,35%' AND custType = 1) 
or (isActive  = 1 AND isActiveAgent  = 1 AND sivug Like '%,35%' AND custType = 3) 
or (isActive  = 1 AND isActiveAgent  = 1 AND sivug Like '%,35%' AND custType  between  5 AND 12 )

i did not want to show all data and i did not notes that on other lines i emited there was an OR

so i had to add it to there too

answered Oct 29, 2012 at 22:26

LoneXcoder

2,1837 gold badges40 silver badges78 bronze badges

1 Comment

Magnus Over a year ago

You can optimize that where with:

...AND isActive = 1 AND isActiveAgent  = 1 AND sivug Like '%,35%' AND (custType = 1 OR custType = 3 OR custType between 5 AND 12)

Collectives™ on Stack Overflow

SQL Server Query with boolean issue

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related