Exception handling in stored procedure in sql server 2000.

Hi all,
I am writing one stored procedure wherein i want to do something like
try-catch.
I am inserting data into one table. So what i want to do is to catch
exception if error occured during insertion.
I want to display generic message if insert query fails.
Can someone tell me how to do this.
Any help will be truely appreciated.
Thanks in advance.
Hi
CREATE TABLE Employees
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL
......
)
BEGIN TRY
INSERT INTO Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL)
PRINT 'After INSERT.'
END TRY
BEGIN CATCH
PRINT 'INSERT failed.'
END CATCH
<trialproduct2004@.yahoo.com> wrote in message
news:1159425651.145892.88090@.e3g2000cwe.googlegrou ps.com...
> Hi all,
> I am writing one stored procedure wherein i want to do something like
> try-catch.
> I am inserting data into one table. So what i want to do is to catch
> exception if error occured during insertion.
> I want to display generic message if insert query fails.
> Can someone tell me how to do this.
> Any help will be truely appreciated.
> Thanks in advance.
>
|||Uri,
Try..Catch block was introduced in SQL 2005.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
|||Hi!
Start with this excellent article by Erland Sommarskog:
http://www.sommarskog.se/error-handling-I.html.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<trialproduct2004@.yahoo.com> wrote in message
news:1159425651.145892.88090@.e3g2000cwe.googlegrou ps.com...
> Hi all,
> I am writing one stored procedure wherein i want to do something like
> try-catch.
> I am inserting data into one table. So what i want to do is to catch
> exception if error occured during insertion.
> I want to display generic message if insert query fails.
> Can someone tell me how to do this.
> Any help will be truely appreciated.
> Thanks in advance.
>
|||Dejan
This is a classic example how to important to mention what is the vesrion of
SQL Server
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:uWW2xss4GHA.1200@.TK2MSFTNGP02.phx.gbl...
> Uri,
> Try..Catch block was introduced in SQL 2005.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
>
|||An even more classic example of how easy it is to overlook said information.
;-)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23NRJNws4GHA.668@.TK2MSFTNGP02.phx.gbl...
> Dejan
> This is a classic example how to important to mention what is the vesrion
> of SQL Server
>
>
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
> message news:uWW2xss4GHA.1200@.TK2MSFTNGP02.phx.gbl...
>
|||Arnie
You are right . :-))) Probably it is because I get in my office on 4AM
today
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O9Uco1s4GHA.1012@.TK2MSFTNGP05.phx.gbl...
> An even more classic example of how easy it is to overlook said
> information.
> ;-)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23NRJNws4GHA.668@.TK2MSFTNGP02.phx.gbl...
>
|||:-)
Hope the rest of the day goes better:-)
Dejan Sarka
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:udc9z6s4GHA.3840@.TK2MSFTNGP03.phx.gbl...
> Arnie
> You are right . :-))) Probably it is because I get in my office on 4AM
> today
>
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:O9Uco1s4GHA.1012@.TK2MSFTNGP05.phx.gbl...
>
|||Hi,
thanks for your example.
But what to do if i have sql server 2000. Is is like that i can't
handle exception in stored procedure itself.
Or is there any alternative?
Any help will be truely appreciated.
thanks in advacne.
|||Hi,,
Thanks to all for reply.
I have one more query regarding exception handling.
I have stored procedure in which i am opening one cursor and after
getting values from cursor i am inserting data into table. So my
question is if insert fails then sp will not execute next part then how
will i deallocate cursor.
Can someone provide me way to do this.
Any help will be truely appreciated.
Thanks in advance.