Scope of an error

[timscronin]

If I have stored proc A which sends a value to be processed by stored proc B, but stored proc b errors out. If I have stored proc B return on error, is the @@error available for Stored proc A, or is the scope limited to B?

 

[SQLDenis]

>>is the @@error available for Stored proc A

yes

example

declare @i int
exec @i = procB
select @i --this will be your error code ,0=Okay

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[timscronin]

Sorry, now have more info from developer. What they are doing is parsing through records and validating xml using system sp. As an example below. Problem is when invalid xml comes through you get system error, which you can correct me on, you really can't trap

alter procedure tim as
DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
<\Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
declare @error int
set @error = @@error
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
if @error <> 0
begin
print 'tom'
end
-- Remove the internal representation.
exec sp_xml_removedocument @hdoc

 

[bborissov]

@@error is a global variable that way it is visible untill you execute next statement

EXEC sp_smonething
IF @@ERROR <> 0
   -- error happened

BUT

EXEC sp_smonething
DECLARE @i int
IF @@ERROR <> 0
   -- that line never be ocuured

From BOL:

Remarks

When Microsoft® SQL Server™ completes the execution of a Transact-SQL statement, @@ERROR is set to 0 if the statement executed successfully. If an error occurs, an error message is returned. @@ERROR returns the number of the error message until another Transact-SQL statement is executed. You can view the text associated with an @@ERROR error number in the sysmessages system table.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

that is a non-trapable error (batch termination). You can not do anything about those errors
Here is a suggestion: First validate the XML file with a parser or schema and then load it into SQL server

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[timscronin]

That's what I figured, kind of suprised MS didn't add that to the system sp, if it fails you are out of luck