create function dbo.ClaimItemStatus ( @ClaimItemPk int, @Start Date, @End Date, @DayLimit Int ) RETURNS TABLE AS with cte as (select case when @Start IS NOT NULL and @End IS NOT NULL THEN 'Settled' WHEN @Start IS NOT NULL case when @DayLimit > (select count(*) .... ) -- here we can either use direct query or your scalar function - I prefer direct count THEN 'OverDue' else 'Pending' END ELSE -- what else is not covered ? 'Unknown' END as StatusDescr) select StatusDescr, case StatusDescr WHEN 'Settled' then 2 when 'Pending' then 4 when 'OverDue' then 3 when 'Unknown' then 5 end as [Status] from cte GOYou can re-write your other functions in a similar way to become inline table-valued functions.
select * from dbo.ClaimItemStatus(1, '20100101','20110101',5) --Or select CI.*, S.* from ClaimItems CI CROSS APPLY dbo.ClaimItemStatus(CI.ClaimID, CI.ClaimStartDate, CI.ClaimEndDate, CI.ClaimDayLimit) S----------------
BEGIN DECLARE @Status INT DECLARE @LapsedDays INT SET @Status = 1 /* if no date forwarded then Pending */ IF NOT @Start IS NULL /* if end date present, then Settled */ IF NOT @End IS NULL SET @Status = 2 /* Settled */ ELSE /* otherwise check if exceeded company limit of days lapsed */ BEGIN SET @LapsedDays = dbo.GetWorkingDays(@Start, GETDATE()) IF @LapsedDays > @DayLimit SET @Status = 3 END RETURN @Status END