Returning Proper Error Value 1

[ewanko]

hi,
i have trouble returning the right error codes. i have a stored procedure like this :

Create Procedure myproc1
...
...
INSERT INTO table1 SELECT * FROM tablesource1
IF @@ERROR < > 0 RETURN 1
...
INSERT INTO table2 SELECT * FROM tablesource2
IF @@ERROR < > 0 RETURN 2

....
INSERT INTO table6 SELECT * FROM tablesource6
IF @@ERROR < > 0 RETURN 6


i purposedly put an error on the first insert

now i tried to get the error code:
DECLARE @error_result smallint
set @error_result = EXEC @myproc1
select ' error code ',@error_result
go

results is:

error code
-6

now it seems SQL doesnt take notice of my own return values.
any help would be appreciated..

ewanko

 

[Liberty]

Change this line:

set @error_result = EXEC @myproc1

to:

exec @error_result = @myproc1

then do a select on the @error_result:

select ' error code ',@error_result

This will do the trick.