I have a process calling a SP which looks like this:
Process
ENTER NEW ForecastInput USING Main
EXC_SP_ForecastPIR
Sub Process - EXC_SP_ForecastPIR
EXEC_SP 'usp_ForecastPIR' WITH '@PartNumber' =ForecastInput.PartNumber ,'@StartDate' = ForecastInput.StartDate , '@EndDate' = ForecastInput.EndDate , '@RunRate' = ForecastInput.RunRate , '@Frequency' = ForecastInput.Frequency , '@Reference' = ForecastInput.Reference , '@Itterations' = ForecastInput.Itterations ,'@Increment' = ForecastInput.Increment OUT
The SP searches another BO called [BOM] (Bill of Material) for matching records, and then iterates through a while loop based off of the StartDate, EndDate and Frequency params, inserting the results into a BO called ForecastOutput. When I try and call the SP via the process, I get the error ‘Internal error. Error converting data type nvarchar to int.’ which would suggest that the datatypes from the input BO are different to the output BO.
The problem is that I have checked every attributes data type, even checking in the advanced section for the exact SQL types and everything matches up exactly. So it makes no sense why I am getting this error.
Has anyone encountered anything similar?
Thanks
SP:
DECLARE @PartNumber AS VARCHAR (200)
DECLARE @StartDate DATE
DECLARE @EndDate AS DATE
DECLARE @RunRate AS INT
DECLARE @Frequency AS VARCHAR (200)
DECLARE @Itterations AS INT
DECLARE @Increment AS INT
DECLARE @Reference AS VARCHAR (200)
SET @Itterations = CASE
WHEN @Frequency = 'Weekly'
THEN DATEDIFF(week,@StartDate,@EndDate)
WHEN @Frequency = 'Fortnightly'
THEN (DATEDIFF(ww,@StartDate,@EndDate)) / 2
WHEN @Frequency = 'Monthly'
THEN DATEDIFF(mm,@StartDate,@EndDate)
END
WHILE @Increment < @Itterations
BEGIN
INSERT INTO [dbo].[BASTESTDOMAINPIR_FORECASTOUTPUT](
[BASTESTDOMAINPIR_FORECASTOUTPUT].[ID]
,[BASTESTDOMAINPIR_FORECASTOUTPUT].[BASVERSION]
,[BASTESTDOMAINPIR_FORECASTOUTPUT].[BASTIMESTAMP]
,[BASTESTDOMAINPIR_FORECASTOUTPUT].[PartNumber]
,BASTESTDOMAINPIR_FORECASTOUTPUT].[Component]
,[BASTESTDOMAINPIR_FORECASTOUTPUT].[Quantity]
,[BASTESTDOMAINPIR_FORECASTOUTPUT].[RequirementDate]
,[BASTESTDOMAINPIR_FORECASTOUTPUT].[Reference]
)
SELECT
(NEXT VALUE FOR [dbo].[BAS_IDGEN_SEQ])
,'1' as BASVERSION
,getdate() as BASTIMESTAMP,
@PartNumber as PartNumber
,[Component]
,[Quantity] * @RunRate as [Quantity]
,@StartDate as RequiermentsDate
,@Reference as Reference
FROM [BASTESTDOMAINPIR_BOM]
WHERE [Material] = @PartNumber
SET @Increment = @Increment + 1 ;
SET @StartDate = CASE WHEN
@Frequency = 'Weekly'
THEN DATEADD(dd, 7, @StartDate)
WHEN @Frequency = 'fortnightly'
THEN DATEADD(dd,14,@StartDate)
WHEN @Frequency = 'Monthly'
THEN DATEADD(dd,30,@StartDate)
END
;
END
