Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-31
9
Medium Priority
?
1,039 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

721 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