Solved

converting a character to an integer

Posted on 2014-11-26
7
102 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 48

Expert Comment

by:Vitor Montalvão
ID: 40468556
You just found a bug in the code.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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