Solved

Error: Syntax error converting the varchar value 'T' to a column of data type int.

Posted on 2004-08-31
9
1,036 Views
Last Modified: 2012-05-05
I am getting the above error message, when trying to typecast a string to an integer and adding 1 to it.

Alter PROCEDURE
Sp_AutoName
@AutoValue varchar OUTPUT
AS

DECLARE @FrmTable varchar

Select top 1  @FrmTable = SUBSTRING(TS_Name,4,4)
FROM Test_Set       
Order by TS_Name Desc

Set @FrmTable = CAST(@FrmTable as int) + 1

Set @AutoValue = 'TS_'+ @FrmTable

return  @AutoValue

When I execute this sp, I am getting the above error messgae in the 'return' statement line.
0
Comment
Question by:sangack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 11939791
Have you checked the value of @FrmTable before trying to cast it?  It looks like it contains the letter "T", in which case, the error is quite correct - you can't convert a "T" to an int.

Why don't you do :

Select top 1  @FrmTable = SUBSTRING(TS_Name,4,4)
FROM Test_Set      
Order by TS_Name Desc

print 'Val = ' + @FrmTable

Set @FrmTable = CAST(@FrmTable as int) + 1

This will print the content of the variable in the debug window.

If you need more help, please update.
0
 
LVL 3

Expert Comment

by:Minna
ID: 11939806
From your select top 1 statement, it returns a varchar value of 't' but in your next statement

  Set @FrmTable = CAST(@FrmTable as int) + 1

You're trying to cast 't' as an integer.

What are you trying to return?  Something like this?

 Set @FrmTable = @FrmTable + '1'

so that @FrmTable = 'T1'?  Please be more specific.




0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11939808
The error is exactly what it says : "Syntax error converting the varchar value 'T' to a column of data type int"
you cannot convert the letter 'T' to a number.

problem 1 you have declared @FrmTable (and @AutoValue) as varchar without specifying a size - this defaults to size 1, which means that it will become the first character of the SUBSTRING(TS_Name, 4,4), not the whole string

problem 2 is that the value assigned to @FrmTable has the letter 'T', which cannot be converted to a number
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:sangack
ID: 11939987
I am trying to Autogenerate a string... like
TS_1, TS_2.... in the below sp.

Alter PROCEDURE
Sp_AutoName
@AutoValue varchar OUTPUT
AS

DECLARE @FrmTable varchar

Select top 1  @FrmTable = SUBSTRING(TS_Name,4,4)
FROM Test_Set       
Order by TS_Name Desc

Set @FrmTable = CAST(@FrmTable as int) + 1

Set @AutoValue = 'TS_'+ @FrmTable

return  @AutoValue

I have check with the select statement and it returns only '3'. Then I am typecasting it to int and adding 1 to it, so @FrmTable should have 4 as value. After that I am appending the incremented value with TS_.
0
 
LVL 10

Expert Comment

by:imrancs
ID: 11940074
Alter PROCEDURE
Sp_AutoName
@AutoValue varchar OUTPUT
AS

DECLARE @FrmTable varchar

Select top 1  @FrmTable = SUBSTRING(TS_Name,4,4)
FROM Test_Set      
Order by TS_Name Desc

Set @FrmTable = Cast( CAST(@FrmTable as int) + 1 as Varchar)

Set @AutoValue = 'TS_'+ @FrmTable

return  @AutoValue


Imran
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11940077
you need to change your data types for your variables to varchar(4) (or longer) to be the correct size. otherwise you will get incorrect results.
otherwise what you expect to be TS_4 will be truncated to 'T'
If this is what you insert into the Test_Set table, then when you pull it back out, the substring will not be numeric
0
 
LVL 10

Expert Comment

by:imrancs
ID: 11940085
and also give the sizes for @FrmTable  and  @AutoValue, as BillAn1 mentioned that it will have size of one char by  default


Alter PROCEDURE
Sp_AutoName
@AutoValue varchar(10) OUTPUT
AS

DECLARE @FrmTable varchar(10)

Select top 1  @FrmTable = SUBSTRING(TS_Name,4,4)
FROM Test_Set      
Order by TS_Name Desc

Set @FrmTable = Cast( CAST(@FrmTable as int) + 1 as Varchar)

Set @AutoValue = 'TS_'+ @FrmTable

return  @AutoValue


Imran
0
 

Author Comment

by:sangack
ID: 11940188
Yes, even I was trying the same thing after seeing BillAn1's comment, but that time i am getting the error message as "Syntax error converting the varchar value 'TS_4' to a column of data type int."
0
 
LVL 10

Accepted Solution

by:
imrancs earned 125 total points
ID: 11940218
this is because of

return  @AutoValue  << return only allowed INT values


you do not need to return becuase  @AutoValue is output variable, and you can access its value from where you are calling it


Imran
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question