Solved

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

Posted on 2004-08-31
9
1,037 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

628 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