Error handling for insert in a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error handling for insert in a SP

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-02-09 : 16:11:16

Here I have created a table
create table table1
( emp_id int,emp_name varchar(50),emp_age int)

I have inserted 2 records

insert into table1 values(123,'test1',25)
insert into table1 values(456,'test2',26)

Here I have stored prcoedure which inserts a record to the same table
How would I captue an error which is caused by trying to insert a varchar value to an int field

CREATE PROCEDURE usp_emp_details_i
AS
insert into table1 values(444,'New','ME')

print @@error
DECLARE @error_int INT
SELECT @error_int=@@error
IF @error_int <> 0
BEGIN
return @error_int
END
GO

declare @error_int int
execute @error_int=usp_emp_details_i
print @error_int

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-02-09 : 16:21:44

Below shown is the error I get but Iam not able to capture this error :-

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

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-09 : 16:23:08

You can't, that's what testing is for. And besides, you would normally accept input parameters for values, and if that were the case, it would fail before it became entrant to the sproc anyway

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-02-09 : 17:15:35

Actually in the stored procedure its and it happend that emp_id in emp_download is varchar and in table1 its int.
All the values that come in are int values in the emp_id.For some reason it came as a varchar and the sp failed and I was trying to catch the error.

insert into table1
(emp_id ,emp_name,emp_age)
select emp_code,emp_name,age from
emp_download

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-02-09 : 17:33:55

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

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 18:16:43

Not related to the problem you have, but:

insert into table1 values(444,'New','ME')
print @@error
DECLARE @error_int INT
SELECT @error_int=@@error

won't work. @@ERROR only stores its value for one statement, then it is reset, so the very next thing AFTER the Insert must be to store the value of @@ERROR into a temporary variable - THEN report on it / perform conditional error handling etc.

"emp_id in emp_download is varchar"

Then you need to perform checks on the data in emp_download before you "import" it into table1. Or change the datatype of emp_id, in the emp_download table, to INT. That will push the "error" further upstream - to the point where the emp_download table is populated (with erroneous data)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-10 : 09:53:01

http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources