Solved

converting a character to an integer

Posted on 2014-11-26
7
97 Views
Last Modified: 2014-11-30
I have a database table named SIP_FLORES_FITS_STATE_CDS (Don't laugh too hard, I didn't name it this, I just maintain the sql)
and this table has three columns in it. The column I am going to be talking about is named FITS_SUFFIX
Here is a sample of some of the data in this column.

FITS_SUFFIX
___________
BAR
CBA
EDF
FFR
GAT

In the stored procedure at line 15 below they are converting a record value in the FITS_SUFFIX table to an integer.
This is where I am getting lost. They are converting a character value to an integer. I can understand how you could
convert the very first character to an integer, but not the entire 3 character code? For example the first record is
BAR, and when that is converted to an integer, what is the output?

Anyway, I did not include all of the stored procedure code, I just included the code below and numbered the lines,
so that I could refer to the line numbers.





1  DECLARE lchSuffix  VARCHAR(3)

2  DECLARE CURS_STATE CURSOR
3   FOR
4     SELECT FITS_STATE_CD, FITS_PREFIX, FITS_SUFFIX
5      FROM SIP_FLORES_FITS_STATE_CDS FITS
6      WHERE FITS.FITS_ACTIVE = 1
7   ORDER BY FITS_STATE_CD
8
9   OPEN CURS_STATE
10  FETCH NEXT FROM CURS_STATE
11  INTO @lchStateCode, @lchPrefix, @lchSuffix
12  WHILE @@FETCH_STATUS = 0
13  BEGIN



14  OPEN CURS_STATE
15  SELECT @lchFITS_SUFFIX = CONVERT(INT, @lchSuffix)
16  SELECT @lchFITS_SUFFIX = @lchFITS_SUFFIX  + 1;
0
Comment
Question by:brgdotnet
7 Comments
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 125 total points
ID: 40468240
Hi,

can you post sample data with where clause like in cursor definition
WHERE FITS.FITS_ACTIVE = 1
. Maybe that data is different from which you posted i.e. maybe (just maybe) it is numeric?
0
 
LVL 9

Assisted Solution

by:teebon
teebon earned 125 total points
ID: 40468418
Hi brgdotnet,

If the value of FITS_SUFFIX is as follows:

BAR
CBA
EDF
FFR
GAT

It will not get thru LINE 15, it will throw a conversion error.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40468556
You just found a bug in the code.
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.

 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 125 total points
ID: 40468595
Add the following code in between line 14 and 15:

print '@lchSuffix = ' + @lchSuffix

Now run the stored proc.  If that part of the code actually gets executed you'll get a message in the Messages output pane which tells you the value of the @lchSuffix variable right before the conversion.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 125 total points
ID: 40470657
Might the 'BAR', etc., values be the prefix, and the suffix is some type of numeric value?
0
 
LVL 9

Expert Comment

by:teebon
ID: 40471187
Hi Scott,

Have you tested running the stored procedure?
0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 40472804
Thanks, working now.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

914 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

22 Experts available now in Live!

Get 1:1 Help Now