Exception Handling In SQL Server

Similar to other programming languages SQL server also supports exception handling i.e handles the exceptions, provide a proper message to the user regarding exception and avoid abnormal termination of the program.

Up to SQL sever 2000, a system variable called @@Error is use to get information about the exception and handle the exception but from SQL Server 2005 onwards exception handling is done by using try catch.

Within SQL server,  a try block can have only one catch block.

Syntax:

         begin try

          <statements>

         end try

          begin catch

          <statements>

         end catch

Error Functions

To get information about raised exception a set of functions are provided, which are collectively called as Error Functions are new in SQL 2005.

• Error_Number() : Returns the unique number of raised error.

• Error_Message() : Returns the message associated with the current  error raised.

• Error_Severity() : Returns the severity of the current error raised.

• Error_State() : Returns the state of current error raised.

• Error_Procedure() : Returns name of the procedure in which exception occurs.

• Error_Line() : Returns the line number within the procedure where error occurs.

Geting List of SQL Server error messages:
select * from sys.messages

You can add your own error messages to the error messages list of SQL server using the stored procedure sp_add message.

Raising Errors Manually:
There are situations where system will not raise an exception for your requirement and you want to raise exception manually for this use the riase error function that has following syntax :

raiserror(msgid/msgtext,severity,state[,arguments])

Triggers

      Triggers are stored sub-programs that will be automatically executed based on specified event.

Differences between Stored Procedures, User Defined Functions and Triggers are as follows-->

• Stored Procedures and User Defined Functions can be called by the user manually but triggers cannot be called by the user manually and they will be automatically invoked.
• Stored Procedures can return a value with output parameters and User Defined Functions can return a value with return statement but triggers cannot return a value either with output parameters or return statement.
• Stored Procedures, User Defined Functions can take arguments but triggers cannot take arguments.

Based on event specified on a trigger, triggers are classified into DDL triggers and DML triggers. When the trigger is created by specifying a DDL command as event then that trigger is called as DDL triggers and when a trigger is created by specifying a DML command then that trigger is called as DML triggers. 

     

      DDL triggers are new in SQL 2005. DML triggers have the following three purposes-->

• Create procedure integrity constraints.
• Record auditing information of a table.
• Allow insert,update,delete on complex views.

Access Modifiers

Members that are defined in type by any scope on specific are always accessible within the type. Restrictions comes into picture only when we try to access them outside of the type. Members declared as private under a class or structure cant be access outside of the type in which they are defined and moreover there default scope is private only.

Types cant be declare as private so private can be use only on members. One point to keep in mind is that Interface cant contain any members and default scope for interface members is public.

• Protected: Members declared as protected under a class be accessed only within the class or in a child class.Non child classes cant consume them. Types cant be declared as protected also, so this can only be used on members.

• Internal: Members and types that are declared as internal can be consumed only within the project bot both from child or non child. The default scope for any type in C# is internal only.

• Protected Internal: Members declared as protected will have dual scope i.e within the project they behave as internal providing access to anywhere in project outside the classes they will change to protected and still provide access to their child classes. Types cannot be declared as protected internal also. So this can also be used only on members.

• Public: A type or member of a type if declared as public is global in scope which can be accessed from anywhere.