catching exception raised by a stored procedure

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)

catching exception raised by a stored procedure

Author

Topic

sirf_atif
Starting Member

4 Posts

Posted - 2004-10-27 : 01:51:22

hi all ..
i have been stuck wid prob. that is i want to catch exception raised by a builtin SP sp_xml_preparedocument

code is

exec sp_xml_preparedocument @iDoc OUTPUT,@XMLDoc

SELECT @cart_customer_uid=loginid, @pword=password from OPENXML (@iDoc, '/data',2) WITH (loginid varchar(10) , password varchar(25))

exec sp_xml_removedocument @iDoc

well if i provide an invalid (missing closing tags, or not properly managed etc) XML to this SP in @XMLDoc it raises an exception n the code following this stops execution..I want to catch this exeption n print it in my own way.

@@error or RAISERROR is not working at all :((

thanx in advance

<sirf_atif>

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-27 : 04:22:54

>> it raises an exception n the code following this stops execution
Do you mean that the code following tdoes not execute and the batch termionates? If so there's nothing you can do about it - you need to handle the error in the client.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-27 : 04:33:13

I tried to wrap the

exec sp_xml_preparedocument @iDoc OUTPUT,@XMLDoc

in a separate SProc, which normally works for me. But it failed :-(


CREATE PROCEDURE dbo.MySProc1
    @iDoc int OUTPUT,
    @XMLDoc ntext
AS 
SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @intErrNo int

SELECT [MySProc1] = 'MySProc1 Start'

exec sp_xml_preparedocument @iDoc OUTPUT,@XMLDoc 
SELECT @intErrNo = @@ERROR

SELECT [MySProc1] = 'MySProc1 End', [@intErrNo]=@intErrNo

RETURN @intErrNo
GO

CREATE PROCEDURE dbo.MySProc2
AS
SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @intErrNo int, @iDoc int

SELECT [MySProc2] = 'Call MySProc1 with good XML'
EXEC @intErrNo = dbo.MySProc1 @iDoc=@iDoc OUTPUT,
     @XMLDoc='<data><loginid>Self</loginid><password>secret</password></data>'
IF @@ERROR <> 0 OR @intErrNo <> 0 SELECT [Error]='Need to handle error'
SELECT [MySProc2] = 'MySProc1 returned'

SELECT [cart_customer_uid]=loginid, [pword]=password 
from OPENXML (@iDoc, '/data',2) 
WITH (loginid varchar(10) , password varchar(25)) 

exec sp_xml_removedocument @iDoc

-- Duff one
SELECT [MySProc2] = 'Call MySProc1 with duff XML'
EXEC @intErrNo = dbo.MySProc1 @iDoc=@iDoc OUTPUT,
     @XMLDoc='<data><loginidSelf</loginid><password>secret</password></data>'
IF @@ERROR <> 0 OR @intErrNo <> 0 SELECT [Error]='Need to handle error'
SELECT [MySProc2] = 'MySProc1 returned'

SELECT [cart_customer_uid]=loginid, [pword]=password 
from OPENXML (@iDoc, '/data',2) 
WITH (loginid varchar(10) , password varchar(25)) 

exec sp_xml_removedocument @iDoc

GO

EXEC dbo.MySProc2
GO

DROP PROCEDURE dbo.MySProc1
GO
DROP PROCEDURE dbo.MySProc2
GO

gives:

Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 12
XML parsing error: A name contained an invalid character.

Kristen

sirf_atif
Starting Member

4 Posts

Posted - 2004-10-28 : 02:54:44

yes nr i mean same..
n also thanx to kirsten

<sirf_atif>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources