>>>>The problem I have is that I get Error Msg 444: Select statements included within a function cannot return data to a client which is due to my SELECT CIT_PK...
>>>>
>>>>How do I return that into a temporary table/cursor so that I can check the status of the claim items?
>>>
>>>It looks like you're overcomplicating the problem. I suggest to create a stored procedure rather than a function and do all the calculations there.
>>
>>What's the difference between the SP and the function? The logic I need to apply will be the same in both, won't it?
>
>Functions in T-SQL have many limitations that SP don't have. Say, you can not use DML statements in functions (except for operations with table variables), functions often don't provide good performance (especially for scalar functions or multi-line table valued functions).
>
>So, I recommend to lean towards stored procedures for complex logic and not try to abuse functions (although, of course, they do have their place).

so are you saying the code I wrote would work in an SP?