SP ERROR HANDLING

[KCWMS]

Using SQL SERVER 2K
the following is the meat-n-potatoes of an sp. It generates an error because there are related child tables that the DELETE would leave orphaned, but my output parameter is never filled with any message and the RETURN value is 0.

What am I not understanding here?

        DELETE FROM dbo.tbl2

	SET IDENTITY_INSERT dbo.tbl2 ON
	INSERT INTO dbo.Biller
		(field1,field2)
	
	SELECT 
		c.field1, c.field2
	FROM	dbo.tbl1 c 

	SET IDENTITY_INSERT dbo.Biller OFF

	--if any errors, return description in @ErrorMsg
	IF @@ERROR > 0
	 BEGIN
	  SELECT @ErrorMsg = [DESCRIPTION]
	  FROM master.dbo.sysmessages
	  WHERE error = @@ERROR
	 END
	
	--exit
	RETURN @@ERROR

 

[micha123]

You have to catch the @@error right after the delete command, since @@error is changed for each command!!!

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[NoCoolHandle]

WHat Micha is getting at is your error code needs to look like

declare @e int
--some code her to cause an error
set @e = @@error
if @e <> 0 
begin
raiserror @e etc....
end

 

[micha123]

Thanks, NoCoolHandler...

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.