>>Hi,
>>
>>I have the following function is SQL Server 2008:
>>
>>

USE [SIAS]
>>GO
>>/****** Object:  UserDefinedFunction [dbo].[GetNextPremiumDue]    Script Date: 02/21/2011 09:42:08 ******/
>>SET ANSI_NULLS ON
>>GO
>>SET QUOTED_IDENTIFIER ON
>>GO
>>-- =============================================
>>-- Author:		<Author,,Name>
>>-- Create date: <Create Date, ,>
>>-- Description:	<Description, ,>
>>-- =============================================
>>CREATE FUNCTION [dbo].[GetNextPremiumDue]
>>(
>>	-- Add the parameters for the function here
>>	@PolicyHeaderPK uniqueidentifier
>>)
>>RETURNS datetime
>>AS
>>BEGIN
>>	-- Declare the return variable here
>>	DECLARE @PremiumDue datetime
>>	
>>	-- Add the T-SQL statements to compute the return value here
>>	DECLARE @DatePaidTo datetime
>>	declare @MonthsToAdd int
>>	declare @PremiumMonths int
>>	declare @IssueDate datetime
>>	
>>	SELECT @MonthsToAdd = SUM(
>>		CASE [PremiumMode] 
>>			 WHEN 'Monthly' THEN [ReceiptDetails].[NumberOfPremiums] * 1
>>			 WHEN 'Quarterly' THEN [ReceiptDetails].[NumberOfPremiums] * 3
>>			 WHEN 'Semi-Annually' THEN [ReceiptDetails].[NumberOfPremiums] * 6
>>			 WHEN 'Annually' THEN [ReceiptDetails].[NumberOfPremiums] * 12
>>			 ELSE 0
>>		END )
>>	FROM [dbo].[ReceiptDetails]
>>	inner join [dbo].[Receipts] on [ReceiptDetails].[ReceiptFK] = [Receipts].[ReceiptPK]
>>	WHERE 
>>		([Receipts].[PolicyHeaderFK] = @PolicyHeaderPK
>>		AND ReceiptTypeFK = 3)
>>
>>	SELECT @IssueDate = [Policies].[IssueDate],
>>			@PremiumMonths = 
>>		CASE [Policies].[PremiumMode] 
>>			 WHEN 'Monthly' THEN 1
>>			 WHEN 'Quarterly' THEN 3
>>			 WHEN 'Semi-Annually' THEN 6
>>			 WHEN 'Annually' THEN 12
>>			 ELSE 0
>>		END 
>>	FROM [dbo].[Policyheaders]
>>		INNER JOIN [dbo].[Policies]	ON [Policyheaders].[PolicyHeaderPK] = [Policies].[PolicyHeaderFK]
>>		INNER JOIN (SELECT [PolicyHeaderFK], MAX(LastChanged) as LastChanged
>>						FROM [dbo].[Policies]
>>						GROUP BY [PolicyHeaderFK]) subquery ON [Policies].[PolicyHeaderFk] = subquery.policyHeaderFK AND Policies.LastChanged = subquery.LastChanged
>>	WHERE 
>>		([Policyheaders].[PolicyHeaderPK] = @PolicyHeaderPK)
>>
>>	SET @DatePaidTo = ISNULL(DATEADD(M, @MonthsToAdd, @IssueDate), @IssueDate)
>>	SET @PremiumDue = DATEADD(M, @PremiumMonths, @DatePaidTo)
>>	
>>	-- Return the result of the function
>>	RETURN @PremiumDue
>>
>>END
>>

>>
>>The problem I am having us that NULL is being returned in the situation where there are no records in the ReceiptDetails table. In this case I would like to set the @DatePaidTo to the @IssueDate, but apparently
>>
>>

SET @DatePaidTo = ISNULL(DATEADD(M, @MonthsToAdd, @IssueDate), @IssueDate)

>>
>>Isn't doing what I thought it would do. Or maybe I'm missing some other logic problem here
>
>Assuming that @IssueDate is not null, it should work. Perhaps @IssueDate is also null here. You may try using
>set @DatePaidTo = COALESCE(dateadd(month, @MonthsToAdd, @IssueDate), @IssueDate, CURRENT_TIMESTAMP) -- to have current date in case of nulls

I tried changing the logic to this:

	IF @MonthsToAdd = 0 OR @MonthsToAdd = NULL
	BEGIN
		SET @DatePaidTo = @IssueDate
	END
	ELSE
	BEGIN
		SET @DatePaidTo = ISNULL(DATEADD(M, @MonthsToAdd, @IssueDate), @IssueDate)
	END

and that has got it, but I'm not sure why :(