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
keithwilson1developerAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
That's basically what that code does.  If the inner SELECT finds something, it will output the value 1, so the WHEN EXISTS in the CASE will be true and Assigned will be "False".  If the SELECT does not find anything, it won't output anything, so the WHEN EXISTS will be false and Assigned will be "True".  To get your Yes and No, just replace False with No and True with Yes.

 If you needed to look for other values, like > 5, then you would have to use Count or something to get a count of the items found, but if you just want to know if anything was found, WHEN EXISTS will do it.

 James
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
'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
0
 
keithwilson1developerAuthor Commented:
I need to do a count and test it to be greater than ZERO
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
keithwilson1developerAuthor Commented:
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
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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 'Yes'
      ELSE 'No'
   END as Assigned
0
 
James0628Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.