Stored Procedure EXEC_SP: Internal error. Error converting data type nvarchar to int.

[JHew]

I have a process calling a SP which looks like this:

Process

Code: Select all

ENTER NEW ForecastInput USING Main
EXC_SP_ForecastPIR

Sub Process - EXC_SP_ForecastPIR

Code: Select all

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:

Code: Select all

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

[Jaymer]

Show us the Server Output section where it shows the call, and the params

[BLOMASKY]

Umm, you did NOT declare your @INCREMENT as OUT. Thats they only way SQL server knows which variable(s) to return.

Also, since this is a stored procedure, there are NO delare statements for the params. just the variable names after the procedure name and before the AS. i.e.

CREATE PROCEDURE Foobar
@param1 INT,
@param2 Varchar(899),
@forcast int OUT

AS
...
...

Bruce

[JHew]

Do I have the wrong syntax?

Code: Select all

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 

I thought putting the "OUT" at the end of the EXEC_SP mean that all params were declared as out.

I've removed the Declare statments but still get the same error as before.

[rbross]

Not sure this is the issue, but make sure the parameters you are passing to the sp do not have any NULL values.
Check them for, IS UNDEFINED. If any of them are update them to ' ' or 0.