All:

I'm having a problem passing the datepart parameter to the DATEADD function where when I pass this parameter as a variable it generates the error: “Error 1023 Invalid parameter 1 specified for date add.”

The code below generates the error:

CREATE TRIGGER AddBudLineItems ON dbo.BudItem
AFTER INSERT
AS
DECLARE
@v_BudId int,
@v_BudItemId int,
@v_BudItemTypeId int,
@v_DueDt datetime,
@v_Notes varchar(512),
@v_StartDT datetime,
@v_EndDt datetime,
@v_Category int,
@v_DaysDue char(7),
@v_MonthsDue char(12),
@v_MonthsDuration int,
@v_DueDayId int,
@v_Occurs int,
@v_PlannedAmt money,
@lv_DatePart varchar(10),
@lv_IntervalQty int,
@lv_OccurStr varchar(10),
@lv_IntervalDate datetime


SELECT
@v_BudId=BudId,
@v_BudItemId=BudItemId,
@v_BudItemTypeId=BudItemTypeId,
@v_StartDT=StartDT,
@v_EndDt=EndDT,
@v_Category=CategoryId,
@v_DaysDue=DaysDue,
@v_MonthsDue=MonthsDue,
@v_MonthsDuration=MonthsDuration,
@v_DueDayId=DueDayId,
@v_Occurs=OccurId,
@v_PlannedAmt=PlannedAmt
FROM INSERTED

SELECT
@lv_OccurStr=
CASE
WHEN @v_Occurs=10 THEN 'dd-1'
WHEN @v_Occurs=20 THEN 'UNKNOWN'
WHEN @v_Occurs=30 THEN 'wk-1'
WHEN @v_Occurs=40 THEN 'wk-2'
WHEN @v_Occurs=50 THEN 'mm-1'
WHEN @v_Occurs=60 THEN 'mm-2'
WHEN @v_Occurs=70 THEN 'mm-3'
WHEN @v_Occurs=80 THEN 'mm-6'
WHEN @v_Occurs=90 THEN 'UNKNOWN'
WHEN @v_Occurs=100 THEN 'UNKNOWN'
WHEN @v_Occurs=110 THEN 'UNKNOWN'
WHEN @v_Occurs=120 THEN 'UNKNOWN'
END
FROM INSERTED


IF @lv_OccurStr <> 'UNKNOWN'
SET @lv_IntervalQty = CAST(SUBSTRING(@lv_OccurStr,4,1) as INT)
SET @lv_DatePart = SUBSTRING(@lv_OccurStr,1,2)
SET @lv_IntervalDate = @v_StartDT
WHILE @lv_IntervalDate < @v_EndDT
BEGIN
INSERT INTO BudLineItem (BudId, BudItemId, BudItemTypeId, DueDt,PlannedAmt)
VALUES (@v_BudId, @v_BudItemId, @v_BudItemTypeId, @lv_IntervalDate, @v_PlannedAmt)

SET @lv_IntervalDate=DATEADD (@lv_DatePart , @lv_IntervalQty, @lv_IntervalDate)

/* IF @lv_DatePart='dd'
SET @lv_IntervalDate=DATEADD (d , @lv_IntervalQty, @lv_IntervalDate)
ELSE IF @lv_DatePart='wk'
SET @lv_IntervalDate=DATEADD (ww, @lv_IntervalQty, @lv_IntervalDate)
ELSE IF @lv_DatePart='mm'
SET @lv_IntervalDate=DATEADD (m, @lv_IntervalQty, @lv_IntervalDate)
*/
END


With the following changes, the error goes away but it add unnecessary code, any ideas here?

IF @lv_OccurStr <> 'UNKNOWN'
SET @lv_IntervalQty = CAST(SUBSTRING(@lv_OccurStr,4,1) as INT)
SET @lv_DatePart = SUBSTRING(@lv_OccurStr,1,2)
SET @lv_IntervalDate = @v_StartDT
WHILE @lv_IntervalDate < @v_EndDT
BEGIN
INSERT INTO BudLineItem (BudId, BudItemId, BudItemTypeId, DueDt,PlannedAmt)
VALUES (@v_BudId, @v_BudItemId, @v_BudItemTypeId, @lv_IntervalDate, @v_PlannedAmt)

--SET @lv_IntervalDate=DATEADD (@lv_DatePart , @lv_IntervalQty, @lv_IntervalDate)

IF @lv_DatePart='dd'
SET @lv_IntervalDate=DATEADD (d , @lv_IntervalQty, @lv_IntervalDate)
ELSE IF @lv_DatePart='wk'
SET @lv_IntervalDate=DATEADD (ww, @lv_IntervalQty, @lv_IntervalDate)
ELSE IF @lv_DatePart='mm'
SET @lv_IntervalDate=DATEADD (m, @lv_IntervalQty, @lv_IntervalDate)

END