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 TABLE t
    (
         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








No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts