Beginner Question for @@ERROR

[mc81773]

I created a stored procedure that will return the error code if it fails. When executing the proc, I get the message like this and what is returned is

Output Messages:
Server: Msg 242, Level 16, State 3, Procedure p_load_data, Line 280
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Under Grids :
-6

Code :
SET NOCOUNT ON

DECLARE @RET_VAL int
EXEC @RET_VAL = p_load_data
SELECT @RET_VAL

Procedure:
CREATE PROCEDURE p_load_data
AS
DECLARE @error_var int
, @Record_Id char(1)
, @Emp_ID varchar (9)
, @FName varchar (50)
, @MName varchar (50)
, @LName varchar (50)
, @BirthDt datetime


DECLARE Stage_Cursor CURSOR
FOR
SELECT Record_Id,Emp_ID,FName,MName,LName,BirthDt
FROM Stage_Table

OPEN Stage_Cursor
FETCH NEXT FROM Stage_Cursor
INTO
@Record_Id,@Emp_ID,@FName,@MName,@LName,@BirthDt
WHILE @@FETCH_STATUS = 0
BEGIN
IF RTRIM(LTRIM(@BirthDt)) = ''
SET @BirthDt = NULL

INSERT INTO Table2(Record_Id,Emp_ID,FName,MName,LName,BirthDt)
VALUES(@Record_Id,@Emp_ID,@FName,@MName,@LName,@BirthDt)
SELECT @error_var = @@ERROR
IF @error_var <> 0
RETURN(@error_var)
END

FETCH NEXT FROM Stage_Cursor
INTO @Record_Id,@Emp_ID,@FName,@MName,@LName,@BirthDt
END
CLOSE Stage_Cursor
DEALLOCATE Stage_Cursor

 

[tlbroadbent]

The error is likely occurring in the FETCH statment because there is invlaid date value in the Stage_Table. Change the declaration of @birthdt to varchar and use the IsDate function to determine if it is a valid date.

IF IsDate(@BirthDt) = 0
Set @birthdt = null

If the dates are stored in the Stage_Table in a format such as dd/mm/yyyy, you'll need to convert this to another format such as yyyy-mm-dd, mmm dd yyyy or mm/dd/yyyy or IsDate will return 0.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[mc81773]

My question was very vague.
I need to know why the error trapping didn't work.
Thanks

 

[sunila7]

Hi,

@@Error is not for any error that occurs in the SP, it is specifically for errors while executing a T-SQL statement...

so, as Terry has said there is an error when assigning an invalid date to a Datetime variable in the Fetch statement...

u can find more about @@Error in msdn

http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409

Sunil

 

[tlbroadbent]

You need to check for an error after each statement that might produce the error. The error trapping didn't work because the error occurs prior to the insert statement. The error code is reset by the time the @@error is checked.

Keep in mind also that fatal errors stop processing and the error ahndling statement will never be executed. In this case, the error is not fatal and you should be able to capture the error. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.