|
How we handel error in stored procedure -- LalitNDubey --
Hello Friends, I am trying to play with stored procedure but get stuck at some where. How the error is being handled in stored procedure. ? Plz reply ASAP Lalit, |
|
-- STSreetharan --
Hi Lalit You can do this very easily, SQL Server is having a global variable called @@error, by checking the value of this variable, you can check this variable you can find whether any error occured or not. Here I am providing a template of this. CREATE PROCEDURE <procedure_name> <@param1> <datatype_for_param1> = <default_value_for_param1>, <@param2> <datatype_for_param2> = <default_value_for_param2> AS --YOUR INSERT/DELETE/UPDATE STATEMENT GOES HERE INSERT INTO <Table_Name> VALUES(.....) IF @@ERROR>0 GOTO ERR1 -- ANOTHER STATEMENT --YOUR INSERT/DELETE/UPDATE STATEMENT GOES HERE DELETE <Table_Name> WHERE <CONDITION> IF @@ERROR>0 GOTO ERR2 -- YOUR PROCEDURE'S LAST STATEMENT RETURN -- EXCEPTION HANDLING PART1 ERR1: RAISERROR('Custom Error Message',15,2) RETURN ERR2: RAISERROR('Custom Error Message For Error2',15,2) RETURN GO To know further about the functionality of each methods, just select them and press ctrl+F1 Regards Sreetharan S.T |
|
-- Pankaj --
Try @@ERROR variable. It will not be equal to zero if some error occured. Pankaj |
|
-- LalitNDubey --
Thanks ST Sreetharan. -----Original Message----- From: TechdotNetIndia Re: How we handel error in stored procedure Hi Lalit You can do this very easily, SQL Server is having a global variable called @@error, by checking the value of this variable, you can check this variable you can find whether any error occured or not. Here I am providing a template of this. CREATE PROCEDURE <procedure_name> <@param1> <datatype_for_param1> = <default_value_for_param1>, <@param2> <datatype_for_param2> = <default_value_for_param2> AS --YOUR INSERT/DELETE/UPDATE STATEMENT GOES HERE INSERT INTO <Table_Name> VALUES(.....) IF @@ERROR>0 GOTO ERR1 -- ANOTHER STATEMENT --YOUR INSERT/DELETE/UPDATE STATEMENT GOES HERE DELETE <Table_Name> WHERE <CONDITION> IF @@ERROR>0 GOTO ERR2 -- YOUR PROCEDURE'S LAST STATEMENT RETURN -- EXCEPTION HANDLING PART1 ERR1: RAISERROR('Custom Error Message',15,2) RETURN ERR2: RAISERROR('Custom Error Message For Error2',15,2) RETURN GO To know further about the functionality of each methods, just select them and press ctrl+F1 Regards Sreetharan S.T |