Here's the query generated by your very strange code:
Select @retvalOUT = Convert(TinyInt,Sunday)
from [dbo].[SLADCSchedule]
where ScheduleStart <= 'Apr 1 2012 12:00AM'
and ScheduleEnd >= 'Apr 1 2012 12:00AM'
and BranchID = 99
What's this supposed to do?
Convert(TinyInt,Sunday)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden