MSSQL Error handling - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

MSSQL Error handling

Author

Topic

thbaig1
Starting Member

8 Posts

Posted - 2011-10-18 : 07:30:05

RDBMS : MSSQL 2005

I am writing stored procedure and using transaction control with Xact_Abort. I want to know how can I check the status of success or failure with Xact_Abort? I want to return error code in case of failure.

The code is given below
.......
.......
BEGIN
SET XACT_ABORT ON
Begin Transaction
--==========================================
-- Insert Customer Info
---=========================================
INSERT
INTO dbo.tblCustomer
(
Name,
[Description],
Title,
IsNewProductNotify
)

values
(
@SiteName,
@Description,
@PageTitle,
@IsAutoProductUpdate
);
-- Storing tblCustomer identity value in vairable for subsequest insert
SELECT @SiteIdent=IDENT_CURRENT('dbo.tblCustomer');

INSERT
INTO dbo.tblPage
(
LogoImage,
WelcomeMessage,
SiteId
)
VALUES
(
@HeaderBanner,
@WelcomeMessae,
@SiteIdent
);

INSERT
INTO dbo.tblFeature
(
MetaDescription,
MetaKeyword,
SiteId
)
VALUES
(
@MetaKeywords,
@MetaDescription,
@SiteIdent
);

Commit Transaction
SET XACT_ABORT OFF
end;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-18 : 07:33:38

Have you tested a try catch block?

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-18 : 07:34:45

Why would you not use TRY CATCH or @@ERROR like everyone else...?

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

thbaig1
Starting Member

8 Posts

Posted - 2011-10-18 : 07:36:51

Thank you.
I am not really familiar with these techniques in MSSQL.
If you may give me a sample to understand or a link to read, it will be great

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-18 : 07:39:44

http://msdn.microsoft.com/en-us/library/ms175976.aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

thbaig1
Starting Member

8 Posts

Posted - 2011-10-18 : 09:14:16

I have modified the code and put the try catch.
I have passed a wrong value to test. I have passed 'a' against bit value. Now I can see on SSMS the error message but my output variable has null rather than error code :(
@ErrorCode is varchar(max) ouput

Kindly tell me where I am wrong?

BEGIN TRY
SET XACT_ABORT ON
Begin Transaction
--==========================================
-- Insert Customer Info
---=========================================
......
......
Commit Transaction
SET XACT_ABORT OFF
end TRY;

BEGIN CATCH
SELECT
@ErrorCode=ERROR_NUMBER()+''+ERROR_MESSAGE() ;
RETURN @ErrorCode;
END CATCH;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-18 : 09:45:52

Are you sure that you are trapping the error and going to the catch block?

begin catch
declare @ErrDesc varchar(max) = coalesce(ERROR_MESSAGE(),'<null>')
declare @ErrProc varchar(500) = coalesce(ERROR_PROCEDURE(),'<null>')
declare @ErrLine varchar(100) = coalesce(ERROR_LINE(),'<null>')
declare @ErrorNo varchar(100) = coalesce(ERROR_NUMBER(),'<null>')

insert Trace (Entity, key1, data1, data2) -- or change this to a select to test
select Entity = @entity, key1 = 'Failure',
data1 = '<ErrProc=' + @ErrProc + '>'
+ '<ErrLine=' + @ErrLine + '>'
+ '<ErrDesc=' + @ErrDesc + '>'
, data2 = '<SQLErrorNo=' + @ErrorNo + '>'
raiserror('Failed %s', 16, -1, @ErrDesc)
end catch

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

thbaig1
Starting Member

8 Posts

Posted - 2011-10-18 : 14:12:12

thank you Lumbago. it is working now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources