How is the error handling in stored proc of T-SQL ?
1 How is the error handling in stored proc of T-SQL 2 What is clustered index and non-clustered index? How many clustered indexes and non-clustered indexes can be created in one table? 3-what is disconnected mode?
Total Answers and Comments: 5
Last Update: February 21, 2009 Asked by: ajay_shu007
In SQL Server 2000 We can check by @@Error<>0 i.e. error. ex: IF @@Error<>0 BEGIN
ROLLBACK TRANSACTIOn
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN
END
Note: You need to check @@Error at every DML statement and since it is Global Variable, So it reset every time.
In SQL Server 2005 We can check by using try catch block ex:
BEGIN TRY
BEGIN TRANSACTION /*statement*/
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
ROLLBACK TRAN
END CATCH
Note: You can check the following functions in catch block a)ERROR_NUMBER() returns no of the error b)ERROR_SEVERITY() returns severity of the error c)ERROR_STATE() returns error state no d)ERROR_PROCEDURE() returns name of stored procedure e)ERROR_LINE() returns line no. f)ERROR_Message() returns the complete text of the message
Above answer was rated as good by the following members: neel_desai
RE: How is the error handling in stored proc of T-SQL ?
In SQL Server 2000 We can check by @@Error<>0 i.e. error. ex: IF @@Error<>0 BEGIN
ROLLBACK TRANSACTIOn
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN
END
Note: You need to check @@Error at every DML statement and since it is Global Variable So it reset every time.
In SQL Server 2005 We can check by using try catch block ex:
BEGIN TRY
BEGIN TRANSACTION /*statement*/
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
ROLLBACK TRAN
END CATCH
Note: You can check the following functions in catch block a)ERROR_NUMBER() returns no of the error b)ERROR_SEVERITY() returns severity of the error c)ERROR_STATE() returns error state no d)ERROR_PROCEDURE() returns name of stored procedure e)ERROR_LINE() returns line no. f)ERROR_Message() returns the complete text of the message
RE: How is the error handling in stored proc of T-SQL ?
Abt Clustered indexes: V can hav only one clustered index in a table by default primary key is termed as clustered index. V can have 249 non-clustered indexes in a table.
0 
1