October 25, 2009 at 4:07 pm
Here is my code:
The problem I am running into is that I can't get the ELSE part of my udf to run when I input this select stmt: select * from dbo.ufn_GenSchedDatesOccur('9/1/2009',DEFAULT,7)
Would any of you expert please point me in the right direction?
Thank you in advance for all of your help.
USE [Sandbox]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_GenSchedDatesOccur] Script Date: 10/25/2009 12:40:39
******/
IF OBJECT_ID (N'dbo.ufn_GenSchedDatesOccur') IS NOT NULL
DROP FUNCTION dbo.ufn_GenSchedDatesOccur
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufn_GenSchedDatesOccur] ( @StartDate datetime, @EndDate datetime = '1/1/1900', @NumOfOccur int = 0)
RETURNS @SchedDates TABLE ( [SchedDate] datetime )
AS
BEGIN
IF @NumOfOccur = 0
BEGIN
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO @SchedDates ( [SchedDate] ) VALUES ( @StartDate )
SET @StartDate = @StartDate + 1
END
END
ELSE
BEGIN
DECLARE @NewOccurEndDateType datetime
SET @NewOccurEndDateType = @NewOccurEndDateType + @NumOfOccur
WHILE @StartDate <= @NewOccurEndDateType
BEGIN
INSERT INTO @SchedDates ( [SchedDate] ) VALUES ( @StartDate )
SET @StartDate = @StartDate + 1
END
END
RETURN
END
October 25, 2009 at 4:30 pm
Hi Experts,
I walked away for a minute and I figured out what I did wrong.
I assigned the wrong value to my declared variable called @NewOccurEndDateType.
You can see what I did originally and then below what I had to do to fix it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufn_GenSchedDatesOccur] ( @StartDate datetime, @EndDate datetime = '1/1/1900', @NumOfOccur int = 0)
RETURNS @SchedDates TABLE ( [SchedDate] datetime )
AS
BEGIN
IF @NumOfOccur = 0
BEGIN
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO @SchedDates ( [SchedDate] ) VALUES ( @StartDate )
SET @StartDate = @StartDate + 1
END
END
ELSE
BEGIN
DECLARE @NewOccurEndDateType datetime
SET @NewOccurEndDateType = (@StartDate + @NumOfOccur)-1
WHILE @StartDate <= @NewOccurEndDateType
BEGIN
INSERT INTO @SchedDates ( [SchedDate] ) VALUES ( @StartDate )
SET @StartDate = @StartDate + 1
END
END
RETURN
END
October 25, 2009 at 9:26 pm
Thanks for the updated code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply