Exception Handling in SQL Server.

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

Exception Handling in SQL Server.

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 02:30:11

Hi Experts..
I am trying to use Exception Handling in SQL SERVER and i am stuck into it as i don't know how it will throw exception. What i have done is..

BEGIN TRY
BEGIN TRAN

CREATE TEMP TABLE

INSERT DATA INTO TEMP TABLE USING EXCEL -- If i put incorrect path so i want it to catch exception but it is not doing that.

THEN there are many insert statment

INSERT INTO TBLA
SELECT * FROM TEMP TABLE

INSERT INTO TBLB
SELECt * FROM TEMP TABLE
..
..
..
..
and so on.

COMMIT
END TRY

BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

END CATCH

This is what i have in my proc. So i want if any error comes up this will go in exception. I try to pass wrong column names in SP for insert but it is not going in exception part even i tried to pass wrong path for Excel Source but also it is not going into exception.
So in what cases this procedure will throw exception.

Thanks in Advance

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 02:40:47

this is from BOL

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

Compile errors, such as syntax errors, that prevent a batch from running.

Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

invalid column name is compile time error so i dont think it will be handled by try catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-15 : 04:47:27

I have few questions in mind. i started a transaction BEGIN TRAN and this inserts data say in temptable from excel sheet and then if an error occured what will happn to my transaction as this is open transaction, not rollbacked never commited.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 04:51:53

if its a runtime error it will captured in catch block where you can do rollback based on error_number value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources