Error trapping fails on INSERT...SELECT
-
I have a stored procedure on which I am trying to trap errors. The central part of the SP is an insert statement. Records are being inserted based on a select statement. When the error happens in the INSERT part of the statement error trapping works fine. For example, the case below is trying to insert a value of 257 into a tinyint field:
INSERT INTO xxxTest (TestNum)
SELECT 257 AS TestNum;
I can trap this error just fine. It goes to my error handler as it should. However, when the error occurs in the select part of the statement, it ignores the error trapping and just prints the error message and number. In the case below, the select statement tries to convert a string into a tinyint.
INSERT INTO xxxTest (TestNum)
SELECT CAST('xxx' AS tinyint) AS TestNum;
Any ideas? The complete SP is below with the problematic INSERT statement highlighted.
Thanks,
Kevin
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE usp_TestErrTrap
AS
DECLARE @ErrNum int
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO xxxTest (TestNum)
SELECT CAST('xxx' AS tinyint) AS TestNum;
SELECT @ErrNum = @@Error;
IF @ErrNum <> 0
BEGIN
GOTO ERR_HANDLER
END
RETURN;
END
ERR_HANDLER:
PRINT 'New record could not be inserted inserted'
RETURN;
GO
-
The error is too severe to be trapped. As soon as the error is found, the execution is stopped, the error is sent to the caller and that's the end of it. I have no clue if there's a work around on that since I ain't no expert on err handling.
-
Are you using 2005? If so, you can wrap it in a TRY...CATCH block
-
Unfortunately, I am using 2000. Try...Catch is not an option.
-
i think in this case, you would want to raise an error if the value being tested('xxx') was not really numeric; there was another thread on this same subject, and two functions were suggested to be used to avoid the use of IsNumeric, which is not the best function for determining whether something is...well...numeric.
code to test:
declare @value varchar(64)
set @value='xxx'
if dbo.isReallyNumeric(@value) = 0
RAISERROR ('The variable is not Really Numeric, and cannot be inserted.',16,1)
else
INSERT INTO #Test select @value
CREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
DECLARE @pos TINYINT
SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))
RETURN CASE
WHEN PATINDEX('%[0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO
CREATE FUNCTION dbo.isReallyInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))
RETURN CASE
WHEN PATINDEX('%[0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
--ddl and dml
drop table t1
create table t1(f1 tinyint)
select * from t1
--the stored procedure
alter procedure ErrorHandler
as
begin
declare @TrappedError int
--TRY
INSERT INTO t1 (f1)
SELECT CAST('257' AS tinyint) AS f1;
SET @TrappedError = @@error
if @TrappedError <> 0
goto error
goto finish
--CATCH
error:
print @TrappedError
--log the error here
INSERT INTO t1 (f1)
SELECT CAST('255' AS tinyint) AS f1;
--FINALLY
finish:
end
--this is how you call the stored procedure
ErrorHandler
jambu
-
As I already said, this error is TOO SEVERE to be trapped in the sp like so (maybe in a nested proc but I would even doubt that).
create table t1(f1 tinyint)
GO
--the stored procedure
create procedure ErrorHandler @SomeValue varchar(5)
as
begin
declare @TrappedError int
--TRY
INSERT INTO t1 (f1)
SELECT CAST(@SomeValue AS tinyint) AS f1;
SET @TrappedError = @@error
if @TrappedError <> 0
goto error
goto finish
--CATCH
error:
print @TrappedError
--log the error here
SELECT 'This will never be executed : There was an error converting the data'
--FINALLY
finish:
end
GO
--this is how you call the stored procedure
exec dbo.ErrorHandler '1'
exec dbo.ErrorHandler 'fail'
GO
SELECT * FROM t1
-- 1 row(s) affected
drop table t1
drop procedure ErrorHandler
-
I ended up moving my error trapping out of the SP and into my calling application (MS Access). The ADO error object has a NativeError property that returns the SQL error message. I am using this to write my error log.
Thanks for all your help.
-
Ya that's one way to go. But this error is severe because it's not something that should happen. If you want to insert a tinyint into a column, then you should pass a tinyint to the procedure, not an unvalidated varchar().
-
In the actual SP, the data is coming from a text file which I am importing into a temp table, doing some processing and inserting into the final table. Bad data in the text file shouldn't be a frequent problem, so I don't want to go through the hassle of validating all of the individual fields (there are a lot). Instead, I just wanted to get an meaningful error number and message when there was a problem. I was hoping to be able to do this in the SP, but it seems easier to take care of it in the calling app.
-
It's like saying I should never have a car accident, I should not be a victime of identity theft.
There's only one way to be safe, be assured that you are covered on all basis!
But that's just my opinion, you do what you want
!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply