Solved

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

Posted on 2004-08-31
9
1,025 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now