CODEWITHSUNDEEP
sql-server
TaylorSmolik
TaylorSmolik

Reputation: 126

Issue converting varchar to INT in sql server

I have seen this question all over stackoverflow, but it seems that there are a wide number of solutions tailored to the situation. It seems I have a unique situation as far as I can tell. I am running this sql statement

use IST_CA_2_Batch_Conversion
GO
--T-SQL script to populate the Match type column
declare @MatchType varchar(16),
@PK varchar(500),
@CAReturnCode VARCHAR(255), 
@CAErrorCodes VARCHAR(255)

declare cursor1 cursor fast_forward for
select 
["Ref#"],
["Return Code"],
["Error Codes"]
from CACodes2MatchType

open cursor1
fetch next from cursor1 into @PK,@CAReturnCode,@CAErrorCodes

while @@fetch_status = 0
begin

set @MatchType = dbo.GetMatchType(@CAReturnCode,@CAErrorCodes)

update CACodes2MatchType
set [Match Type] = @MatchType
where ["Ref#"] = @PK

fetch next from cursor1 into @PK,@CAReturnCode,@CAErrorCodes
end
close cursor1
deallocate cursor1

It will fail at 

set @MatchType = dbo.GetMatchType(@CAReturnCode,@CAErrorCodes)

Here is the beginning code for the GetMatchType function:

-- Batch submitted through debugger:    
 SQLQuery14.sql|6|0|C:\Users\b01642a\AppData\Local\Temp\~vs1C8E.sql
 CREATE FUNCTION [dbo].[GetMatchType](@CAReturnCode VARCHAR(255), @CAErrorCodes    
 VARCHAR(255))
 RETURNS VARCHAR(16)
 BEGIN 
  DECLARE @MatchType VARCHAR(16);
  DECLARE @errorCodes TABLE(Pos INT, Code CHAR(2));
  DECLARE @country INT;   -- 1 is US, 2 is Canada
  DECLARE @numMinorChanges INT;
  DECLARE @numMajorChanges INT;
  DECLARE @numSingleCodes INT;
  DECLARE @returnCode INT;

  DECLARE @verified VARCHAR(16);
  DECLARE @goodFull VARCHAR(16);
  DECLARE @tentativeFull VARCHAR(16);
  DECLARE @poorFull VARCHAR(16);
  DECLARE @multipleMatch VARCHAR(16);
  DECLARE @unmatched VARCHAR(16);

  SET @verified = 'Verified';
  SET @goodFull = 'Good Full';
  SET @tentativeFull = 'Tentative Full';
  SET @poorFull = 'Poor Full';
  SET @multipleMatch = 'Multiple Match';
  SET @unmatched = 'Unmatched';

  SET @returnCode = CAST(@CAReturnCode AS INT);

I will get the error: Msg 245, Level 16, State 1, Line 21 Conversion failed when converting the varchar value '"1"' to data type int.

This error occurs at the last line of the code segment I have shown:

SET @returnCode = CAST(@CAReturnCode AS INT);

This is code that was written by a colleague and supposedly had worked for him. I have had to troubleshoot some errors but I cannot debug this one. I understand alot of people will create a dbo.split function? I don't know if this option will help me in this scenario. I have tried setting @returnCode to a varchar and getting rid of the CAST on @CAReturnCode. As a result, the debugger will make it past that line but raises issues with the rest of the code. I am assuming there is an issue with how I am casting @CAReturnCode? Any help would be much appreciated.

Upvotes: 0

Views: 2909

Answers (1)

Adam Anderson
Adam Anderson

Reputation: 508

The problem is that @CAReturnCode contains non-numeric characters.

-- Msg 245, Level 16, State 1, Line 21 Conversion failed when converting the varchar value '"1"' to data type int.

See, the outer single quotes are the error message's formatting, but the inner double quotes are in the @CAReturnCode value. So the solution here is to ensure that the variable contains only numeric characters prior to converting. If double quotes are the only possibility, you can do a quick and dirty fix like this:

set @returnCode = cast(replace(@CAReturnCode, '"', '') as int)

If there are more possibilities, you could do multiple REPLACE calls, or you could build a better character-trimming function that will remove all the characters you specify at once yourself.

Upvotes: 2

Related Questions