Solved

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

Posted on 2004-08-31
9
1,028 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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 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 setup several different housekeeping processes for a SQL Server.

778 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