Solved

converting a character to an integer

Posted on 2014-11-26
7
107 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
[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
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 50

Expert Comment

by:Vitor Montalvão
ID: 40468556
You just found a bug in the code.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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.

739 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