none
Sqlserver

    Question

  • In Oracle to handle the general exceptions we will write the program as

    procedure raise_exc(i number,msg out varchar) as
     i_exc exception;
    begin
     if i>10 then
      raise i_exc;
    end if;
    exception
    when i_exc then
    msg:='Value of i <10');
    end;
    /


    In case of Sqlserver program how to raise these type of exceptions and how to handle it
    Monday, June 25, 2007 6:25 AM

Answers

  • In SQL Server almost same. But you have to use the raiserror statement.

     

    Raiserror can throw the error message on the fly, or from the sysmessage system table. if you have group of messages and want to use accross the application then you can go for sysmessage table. here i demonstrated both the ways.

     

    On the fly:

     

    Code Snippet

    RAISERROR  ('I am your known errormessage', 16, 1)

                --Here 16 is severity; See Books Online to know more about severity under Raiserror chapter;

                --16 will indicate these are custom

                --1 State; See Books Online to know more about State under Raiserror chapter;

     

    RAISERROR  ('I am your known errormessage, Param 1: %s,Param 2: %d', 16, 1,'First Param', 99)

                --Here 16 is severity; See Books Online to know more about severity under Raiserror chapter;

                --16 will indicate these are custom

                --1 State; See Books Online to know more about State under Raiserror chapter;

     

     

    Using SysMessages

     

    Code Snippet

    --First Add your messages on the SysMessages

    --Note: Error numbers should be greater than 50000

     

     

    EXEC sp_addmessage 50001, 16, N'I am your known Error Message'

     

    EXEC sp_addmessage 50002, 16, N'I am your known Error Message Param 1 :%s Param 2 :%d'

     

    Go

     

    --Reuse the Added Message Numbers on the raiserror statement

     

    Raiserror (50001, 16, 1)

     

    Raiserror (50002, 16, 1, 'Param Value', 99)

     

    To handle the exception in sql server is bit different. SQL Server 2000 proc execution is ON ERROR RESUME NEXT. So you have to test the supecious error on after the execution of every line using @@ERROR variable. If @@ERROR=0 then NO ERROR Else SOME ERROR OCCURED. You can't suppress the error messagge...

     

    Code Snippet

    Declare @LocalError as Int

    Select 1/0;

    If(@@Error <>0)

    Begin

                Raiserror('Divid By Zer0', 16, 1,1)

    End

     

     

    In SQL Server 2005, you can use the TRY..CATCH block (like other programming languages).

    Monday, June 25, 2007 6:53 AM

All replies

  • In SQL Server almost same. But you have to use the raiserror statement.

     

    Raiserror can throw the error message on the fly, or from the sysmessage system table. if you have group of messages and want to use accross the application then you can go for sysmessage table. here i demonstrated both the ways.

     

    On the fly:

     

    Code Snippet

    RAISERROR  ('I am your known errormessage', 16, 1)

                --Here 16 is severity; See Books Online to know more about severity under Raiserror chapter;

                --16 will indicate these are custom

                --1 State; See Books Online to know more about State under Raiserror chapter;

     

    RAISERROR  ('I am your known errormessage, Param 1: %s,Param 2: %d', 16, 1,'First Param', 99)

                --Here 16 is severity; See Books Online to know more about severity under Raiserror chapter;

                --16 will indicate these are custom

                --1 State; See Books Online to know more about State under Raiserror chapter;

     

     

    Using SysMessages

     

    Code Snippet

    --First Add your messages on the SysMessages

    --Note: Error numbers should be greater than 50000

     

     

    EXEC sp_addmessage 50001, 16, N'I am your known Error Message'

     

    EXEC sp_addmessage 50002, 16, N'I am your known Error Message Param 1 :%s Param 2 :%d'

     

    Go

     

    --Reuse the Added Message Numbers on the raiserror statement

     

    Raiserror (50001, 16, 1)

     

    Raiserror (50002, 16, 1, 'Param Value', 99)

     

    To handle the exception in sql server is bit different. SQL Server 2000 proc execution is ON ERROR RESUME NEXT. So you have to test the supecious error on after the execution of every line using @@ERROR variable. If @@ERROR=0 then NO ERROR Else SOME ERROR OCCURED. You can't suppress the error messagge...

     

    Code Snippet

    Declare @LocalError as Int

    Select 1/0;

    If(@@Error <>0)

    Begin

                Raiserror('Divid By Zer0', 16, 1,1)

    End

     

     

    In SQL Server 2005, you can use the TRY..CATCH block (like other programming languages).

    Monday, June 25, 2007 6:53 AM
  • Thank u sekaran
    Monday, June 25, 2007 7:20 AM