Solved

converting a character to an integer

Posted on 2014-11-26
7
94 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 45

Expert Comment

by:Vitor Montalvão
ID: 40468556
You just found a bug in the code.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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.

Join & Write a Comment

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

15 Experts available now in Live!

Get 1:1 Help Now