Solved

# converting a character to an integer

Posted on 2014-11-26
107 Views
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
Question by:brgdotnet
[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

LVL 15

Assisted Solution

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

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 50

Expert Comment

ID: 40468556
You just found a bug in the code.
0

LVL 37

Assisted Solution

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

Scott Pletcher 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

ID: 40471187
Hi Scott,

Have you tested running the stored procedure?
0

LVL 2

Author Closing Comment

ID: 40472804
Thanks, working now.
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
###### Suggested Courses
Course of the Month8 days, 4 hours left to enroll