| Author |
 Topic  |
|
|
vinod koti
Starting Member
India
2 Posts |
 Posted - 11/14/2012 : 09:12:46
|
Hi All,
I am getting below error while executing the SP.
Error:Conversion failed when converting the VARCHAR value '41000-0345' to data type INT
I am looking forward for your help to fix this.
USE [Production]
GO
/****** Object: StoredProcedure [dbo].[usp_NextStatus_EvaluateCriteria] Script Date: 11/14/2012 19:29:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[usp_NextStatus_EvaluateCriteria]
@Debug int = 0,/*Debug flag 0=OFF; 1=PRINT; 2=PRINT+AUDIT */
@BatchUID uniqueidentIfier,/*BatchUID */
@AppCode varchar(4)='', /*Appcode for app in workflow */
@CriteriaNbr int, /*Criteria number n of Cn cindtition */
@CriteriaClass int, /*Type of Creteria */
@UpdateAmount int=0, /*IF this criteria updates amount */
@CurrencyFactor int=100, /*Divide by this value when converting
value (with no decimal places) to get
the currency value */
@NumCurDigits int=2, /*Number of significant decimal places*/
@Subject1Type varchar(255) = '',
@Subject1ItemType varchar(255) = '',
@Subject1 varchar(255) = '',
@Expression varchar(255) = '',
@Subject2Type varchar(255) = '',
@Subject2ItemType varchar(255) = '',
@Subject2 varchar(255) = '',
@CommonAmtList varchar(4096)= '' /*List of common amounts;used for constant type*/
AS
BEGIN
SET NOCOUNT ON
DECLARE @strProcName varchar(255)
DECLARE @strDebugText varchar(4096)
SET @strProcName = 'usp_NextStatus_EvaluateCriteria'
IF @Debug > 0
BEGIN
SET @strDebugText = 'C'+CAST(@CriteriaNbr as varchar(2))+';'+
'@Subject1Type='+@Subject1Type+';'+
'@Subject1ItemType='+@Subject1ItemType+';'+
'@Subject1='+@Subject1+';'+
'@Expression='+@Expression+';'+
'@Subject2Type='+@Subject2Type+';'+
'@Subject2ItemType='+@Subject2ItemType+';'+
'@Subject2='+@Subject2+';'+
'@CommonAmtList='+@CommonAmtList+';'+
'@CriteriaClass='+CAST(@CriteriaClass as varchar(2))+';'+
'@UpdateAmount='+CAST(@UpdateAmount as varchar(2))+';'
EXEC usp_NextStatus_DebugTrace @Debug,@strProcName,@AppCode,@strDebugText,@BatchUID
END/* @Debug > 0 */
DECLARE @intErrorReturn int, @intRowCount int
SET @intErrorReturn = 0
IF @CriteriaClass = 1 or /* =@cintBooleanCrit */
@CriteriaClass = 2 /* =@cintConstantCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalConstantCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @UpdateAmount, @Subject1ItemType,
@Subject1, @Expression, @Subject2, @CommonAmtList
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 3 /* @cintItemCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalItemCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @UpdateAmount, @Subject1Type,
@Subject1ItemType, @Subject1, @Expression, @Subject2Type,
@Subject2ItemType, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 4 /* @cintTransCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalTransactionCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @UpdateAmount,
@CurrencyFactor, @NumCurDigits, @Subject1Type,
@Subject1ItemType, @Subject1, @Expression, @Subject2Type,
@Subject2ItemType, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 5 /* @cintBatchCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalBatchCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @UpdateAmount, @Subject1Type,
@Subject1ItemType, @Subject1, @Expression,
@Subject2Type, @Subject2ItemType, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 6 or/* @cintAllItemsTransCrit */
@CriteriaClass = 8 /* @cintAmountAppliedCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalAllItemsTransCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass,
@Subject1ItemType, @Subject1, @Expression, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 7 /* @cintAllItemsBatchCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalAllItemsBatchCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @Subject1ItemType, @Subject1,
@Expression, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 10 /* @cintAllSameTransCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalAllSameTransCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @Subject1ItemType, @Subject1,
@Expression, @Subject2ItemType, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 11 /* @cintAllSameBatchCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalAllSameBatchCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @Subject1ItemType, @Subject1,
@Expression, @Subject2ItemType, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 12 /* @cintAllItemsCountCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalAllItemsCountCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @UpdateAmount, @Subject1Type,
@Subject1ItemType, @Subject1, @Expression,
@Subject2Type, @Subject2ItemType, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 13 /* @cintBatchCountCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalBatchCountCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @UpdateAmount, @Subject1Type,
@Subject1ItemType, @Subject1, @Expression,
@Subject2Type, @Subject2ItemType, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 14 /* @cintItemCountCrit */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_EvalItemCountCriteria
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass, @UpdateAmount, @Subject1Type,
@Subject1ItemType, @Subject1, @Expression,
@Subject2Type, @Subject2ItemType, @Subject2
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
ELSE
IF @CriteriaClass = 15 /* @cintMultiMultiPD */
BEGIN
EXECUTE @intErrorReturn = usp_NextStatus_MultiMultiPD
@Debug, @BatchUID, @AppCode,
@CriteriaNbr, @CriteriaClass
IF (@intErrorReturn <> 0) GOTO usp_NextStatus_EvaluateCriteria_Error
END
RETURN(0)
usp_NextStatus_EvaluateCriteria_Error:
IF @Debug > 0
BEGIN
SET @strDebugText = 'Error:' + CAST(@intErrorReturn as varChar(20))
EXEC usp_NextStatus_DebugTrace @Debug,@strProcName,@AppCode,@strDebugText,@BatchUID
END/* @Debug > 0 */
RETURN(@intErrorReturn)
END
vinod Koti |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/14/2012 : 09:18:47
|
I didn't look thru your code, but quite simply '41000-0345' just isn't an integer. Do want the answer to be 41000 minus 345? If you don't need it to be an integer, then change the data type where you're using it to be varchar(n)
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
vinod koti
Starting Member
India
2 Posts |
Posted - 11/14/2012 : 09:36:11
|
quote:
Originally posted by jimf
I didn't look thru your code, but quite simply '41000-0345' just isn't an integer. Do want the answer to be 41000 minus 345? If you don't need it to be an integer, then change the data type where you're using it to be varchar(n)
Jim
Everyday I learn something that somebody else already knew
Hi JIM,
Thanks for your quick response, i replaced - with 0 and issue got solved. But i am getting new error
"-2147217833 The conversion of the varchar value '4100000345' overflowed an int column"
Could please suggest the cause for this.
vinod Koti |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/14/2012 : 09:42:41
|
If that's the right value, then you'd need to the BIGINT data type. INT only goes up to 2,147,483,647, BIGINT gets you to 9,223,372,036,854,775,807
Jim
Everyday I learn something that somebody else already knew |
|
|
| |
Topic |
|
|
|
Conversion failed when converting the VARCHAR valu
