Conversion failed when converting the VARCHAR valu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Conversion failed when converting the VARCHAR valu

Author

Topic

vinod koti
Starting Member

2 Posts

Posted - 2012-11-14 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 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

2 Posts

Posted - 2012-11-14 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources