THROW new function in Denali

THROW

It raise an exception and will transfer the code to CATCH block,while using TRY CATCH block
If not used in TRY CATCH then batch will be aborted.

SYNTAX

THROW [ { error_number | @local_variable },
        { message | @local_variable },
    { state | @local_variable }
] [ ; ]

Error_number :- must be greater than 50000 , can be any one. Can be a variable also

Message :-  A custom message , dont need to be a printf like raiserror. Can be a variable also

State:- A value between 0 and 255. Indicates state of a statement associated. I am not sure how to use it till now. 🙂

1. If not used in try catch then batch will be aborted
2 If used in try then parameters should be passed
3 No need of parameter if used in CATCH
4.In TRY block the previous satement before catch must end with ;

A better idea can be achieved with Example

CREATE TABLEt

    (

         id   INT PRIMARY KEY,

         data VARCHAR(4)

    )

We will raise an error by inserting string lengh more than specified length in table

 BEGIN TRY

    INSERT INTO t

    VALUES      ( 1,

                  ‘data’)

    INSERT INTO t                — THIS WILL FAIL

    VALUES      (2,

                 ‘data1234’)

END TRY

BEGIN CATECH

    SELECT Error_message()

END CATCH

Now we will raise same error by using throw statement

 BEGIN TRY

    INSERT INTO t                — THIS WILL FAIL

    VALUES      ( 3,

                  ‘data1234’)

END TRY

BEGIN CATCH

    THROW

END CATCH

Now we want to issue throw in TRY block to generate custom error. Code after throw will be aborted

BEGIN TRY

   INSERT INTO t

   VALUES      ( 3,

                 ‘data’);

   THROW 242432,‘Why are you inserting data ?’,1

   INSERT INTO t                — THIS WILL ABORTED

   VALUES      ( 4,

                   ‘data’);

END TRY

BEGIN CATCH

    THROW

END CATCH

SELECT * FROM T