>I have the following function in place but it doesn't seem to be working in all cases. The function is supposed to return the Students effective primary insurance at the time of a therapy transaction. The business rules in place are only one primary insurance can be effective at a time. In this case the Therapy Transaction Date is 2/28/2014 and there is one insurance policy that has an effective date of 1/1/2008 and a termination date of 2/28/2014 and the type is primary. Any suggestions on what I'm missing here?
>
>TIA
>Jeff
>
>

>USE [SMS]
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>
>-- =============================================
>-- Author:		Jeff Hibbs
>-- Create date: March 23, 2010
>-- Description:	Returns Current Current Primary Insurance
>-- =============================================
>CREATE FUNCTION [dbo].[GetPrimaryInsurance] 
>
>(-- Student Id to be passed into function, as Student
> -- can only have one primary at any given time.
> @tStudentID Char(36),
> @tDate datetime
>)
>
>RETURNS varchar(50)
>AS
>
>BEGIN
>	
>	DECLARE  @tcCompany_Name varchar(50)		
>
>	-- Get Current Term
>	SET @tcCompany_Name = (select cCompany_Name from student_insurance where cStudent_cId = @tStudentID AND
> deffective <= @tDate and (dtermination > @tDate or dtermination is NULL) and ctype = 'Primary')  
>	
>    RETURN @tcCompany_Name
>
>END
>

What is the exact problem here? What does it return?

I would use select @tcCompany_Name = [Name] from ...

Also, does it have to be the scalar function? Inline table valued functions perform better in SQL Server.

Finally, there is a minor typo in the description to this function - the word 'Current' is duplicated.