@@Error problem

[Thrakazog]

Hi,

I've got a stored procedure where I need to convert values to an int. If the data can't be converted, SQL is throwing an error that I can't catch in the stored procedure. Here is some example code:

DECLARE @ID int
DECLARE @err int
SET @ID = 12

SET @ID = cast('CauseError' as int) --Causes an Error
SELECT @err = @@error
IF @err <> 0
BEGIN
print 'error' --NEVER GETS HERE!
SET @ID = 0
END
select @ID

None of the code after the line with the error is run. Can anyone tell me how to catch this error inside the stored procedure so I can handle it there?

Thanks,

 

[mrdenny]

That's not going to work. SQL Error catching doesn't work like you would expect it would. It's manly usefull for catching errors being reported by called stored procedures, and insert failures.

A failure like this should fail with a high enough severity level that the code stops in it's tracks.

Can you provide the full error message being reported and I'll confirm this for you.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Thrakazog]

There error I get is this:

Server: Msg 245, Level 16, State 1, Line 5
Syntax error converting the varchar value 'CauseError' to a column of data type int.

 

[mrdenny]

Yeah, that's what I thought. A level 16 error will stop the sql statement in it's tracks.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[cmmrfrds]

You could clean up the data and send something back to the app indicating the type of data that was encountered.

declare @thevalue int
declare @yourvar varchar(10)
--select @yourvar = 'a1'
--select @yourvar = null
select @yourvar = '77'

select @thevalue =
(case when isnumeric(@yourvar)=0 then 9999
when @yourvar is null then 9998
else cast (@yourvar as int) end)
select @thevalue