SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Conversion failed when converting the VARCHAR valu
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vinod koti
Starting Member

India
2 Posts

Posted - 11/14/2012 :  09:12:46  Show Profile  Reply with Quote
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
2869 Posts

Posted - 11/14/2012 :  09:18:47  Show Profile  Reply with Quote
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
Go to Top of Page

vinod koti
Starting Member

India
2 Posts

Posted - 11/14/2012 :  09:36:11  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/14/2012 :  09:42:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000