select a.assignment_id,
       s.student_id,
       s.student,
       e.elective,
       a.isLottery,
       a.status,
       b.start_dt,
       b.end_dt,
       s.coord_notes,
       a.assign_dt
from
(select assignment_id,
        block_id,
        student_id,
        status,
        isLottery,
        assign_dt
from assignments) as a
inner join
(select block_id,
        elective_id,
        start_dt,
        end_dt
from block_schedule) as b
on a.block_id=b.block_id
inner join
(select elective_id,
        elective_name as elective
from electives) as e
on b.elective_id=e.elective_id
inner join
(select student_id as student_id,
        lastname + ', ' + firstname + ' ' + middlename as student,
        isNULL(coord_notes,' ') as coord_notes,
        active
from student where active=@active) as s
on a.student_id=s.student_id
CASE s.student
  WHEN '' THEN
     where ( status like '%' + @status + '%' and 
	 year(start_dt) =@year and active=@active)
ELSE
     where (s.student like '%' + @search + '%') 
END
order by s.student, e.elective,b.start_dt

where (( status like '%' + @status + '%' and 
	 year(start_dt) =@year and active=@active and s.student='')
or (s.student like '%' + @search + '%'))