asked on

Convert a SQL value to Boolean

In my "IIF", I am trying to convert the value to boolean and getting an error.

CREATE PROCEDURE ssrSP_getCourse_Campus
@CourseNum as varchar(10),
@SessionNum as int,
@yrId as int=5
AS
select
sc.schoolId as CampusId,
sc.schcode,
sc.SchoolName,
cc.courseNumber,
(select count(*)
      from course_type_session ct
       join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
       join Session s on ss.SessionId = s.Id
       join course c on c.Id = ct.courseId
      where c.coursenumber = cc.courseNumber
       and c.schoolCode = sc.schCode
       and s.SchYearId = @yrId
       and s.SessionNo = @SessionNum) as CntSession,

IIf((select count(*)
      from course_type_session ct
       join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
       join Session s on ss.SessionId = s.Id
       join course c on c.Id = ct.courseId
      where c.coursenumber = cc.courseNumber
       and c.schoolCode = sc.schCode
       and s.SchYearId = @yrId
       and s.SessionNo = @SessionNum) > 0,"No", "Yes") as Assigned

from school sc
join course_catalog cc on sc.academiclevelid = cc.Academiclevelid
where cc.coursenumber = @CourseNum
and sc.SSCampus = 1
and sc.Active = 1
and sc.schoolYearId = @yrId
GO

Aneesh

'IIF' wont work with SQL, you need to make use of Case statement

Case when exists (select 1
from course_type_session ct
join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
join Session s on ss.SessionId = s.Id
join course c on c.Id = ct.courseId
where c.coursenumber = cc.courseNumber
and c.schoolCode = sc.schCode
and s.SchYearId = @yrId
and s.SessionNo = @SessionNum) Then 'False' else 'True' End as Assigned

keithwilson1

ASKER

I need to do a count and test it to be greater than ZERO

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keithwilson1

ASKER

So what is wrong with this code?
I am getting Error 170 Invalid syntax near '>'
CASE
WHEN exists (select count(*)
      from course_type_session ct
       join schoolSession ss on ct.schoolSessionId = ss.schoolSessionId
       join Session s on ss.SessionId = s.Id
       join course c on c.Id = ct.courseId
      where c.coursenumber = cc.courseNumber
       and c.schoolCode = sc.schCode
       and s.SchYearId = @yrId
       and s.SessionNo = @SessionNum) > 0 THEN 'Yes'
ELSE 'No'
END as Assigned

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James0628

You've probably figured this out, but just in case ...

The error was because you were trying to do two different tests, EXISTS and >, on the results of one SELECT statement. You have to use one or the other, and aneeshattingal's code with EXISTS seems more efficient that producing and testing a count.

Glad I could help.

James