Problem with control error in fecth

Question

Post reply

Problem with control error in fecth

jose luis portela

SSC Journeyman

Points: 81
More actions
August 23, 2006 at 12:03 pm

#114216

I am having the following problem and I do not know as to treat it in the SQL Server 2000.    It follows the example below  :
/*
CREATE TABLE dbo.teste
(
    teste varchar(18) NOT NULL
)
go
insert into teste values (1)
insert into teste values (2)
insert into teste values (3)
insert into teste values (4)
insert into teste values ('XX')
*/
DROP PROCEDURE dbo.AA_TESTE
GO
CREATE PROCEDURE dbo.AA_TESTE

AS
BEGIN
DECLARE @CONT        NUMERIC(18)
------------------------------------------
DECLARE C_TESTE INSENSITIVE CURSOR
FOR
SELECT TESTE
FROM TESTE
------------------------------------------
OPEN C_TESTE
FETCH NEXT FROM C_TESTE INTO @CONT
WHILE @@FETCH_STATUS = 0
   BEGIN
FETCH NEXT FROM C_TESTE INTO @CONT
   IF @@ERROR <> 0 then
      PRINT 'ERROR FETCH '
   END
CLOSE      C_TESTE
DEALLOCATE C_TESTE
END
GO
The problem that I have is following after fecth of the cursor to receive the value “XX” I does not obtain to treat the error in the SQL SERVER. It simply aborts giving an error of convert to varchar to numeric. it does not pass for the @@error.I ask, exists some form to deal with this error fetch without having that to test variable is numeric

Tanks

Jose Luis

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32470 More actions · Answer 1

not sure if it will work, but you will need an error check too at

OPEN C_TESTE

FETCH NEXT FROM C_TESTE INTO @CONT

->error check

jose luis portela SSC Journeyman Points: 81 More actions · Answer 2

it´s true Jo Pattyn, however with @@ERROR after open, it does not enter ......

Sergiy SSC Guru Points: 110209 More actions · Answer 3

DECLARE C_TESTE INSENSITIVE CURSOR

FOR

SELECT TESTE

FROM TESTE

WHERE TESTE NOT LIKE '%[^0-9]%]

Not numeric values just filtered out from selected set. No need to check for errors.

_____________
Code for TallyGenerator

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 4

Sergiy: Your solution is fetching the 'XX' Value also

The following will fetch only numerics

DECLARE C_TESTE INSENSITIVE CURSOR

FOR

SELECT TESTE FROM TESTE WHERE TESTE LIKE '%[0-9]%'

But I guess this is not the question, question is about "Control is not going to the Next statement after generating error"

jose luis portela SSC Journeyman Points: 81 More actions · Answer 5

Yes,

You´re right Gopi the quetion is Control is not going to the Next statement after generating error"

tanks Gopi ...tanks

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Nope, sorry Gopi... Serqiy has it right (once you correct the typeo) and you have it wrong... here's the proof... run this in the grid mode so you can "see"...

DECLARE @TestVar VARCHAR(10)

SET @TestVar = '1X' --Cannot be converted into an INT

--===== Serqiy's method correctly returns nothing

SELECT 'Seqiy method prevents this from displaying',@TestVar

WHERE @TestVar NOT LIKE '%[^0-9]%'

--===== Gopi's method incorrectly allows it in

SELECT 'Gopi method allows inconvertable data',@TestVar

WHERE @TestVar LIKE '%[0-9]%'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 7

The best way to treat errors is to not allow them at all (which is what Sergiy posted), and the best way to write cursors is to not write them at all 🙂

Are you sure that you need the cursor, Jose? What are you trying to do in the procedure? Should it perform some action, or is it just part of learning SQL?

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 8

Jeff, you are right.. my method will work for for single characters only

Thanks

John Beggs SSCommitted Points: 1921 More actions · Answer 9

Out of curiosity, what is the final goal here?

Is simply never selecting the non-numeric results the complete solution or do you need to do something with those records?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 10

Thanks for the feedback, Gopi...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)