Solved

SAMPLE PROCEDURES  WITH EXCEPTION HANDLING IN THE MSSQL

Posted on 2008-10-07
6
1,135 Views
Last Modified: 2012-05-07
SAMPLE PROCEDURES WITH EXCEPTION HANDLING IN THE MS SQL
0
Comment
Question by:pavanark
6 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 336 total points
ID: 22657097
basically in sql server 2000 the only way to perform execption handling is by useing the @@Error variable which holds the return code of each executed statement
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22657098
could you please clarify what you are looking for, and for which version of mssql server?
0
 
LVL 14

Assisted Solution

by:rachitkohli
rachitkohli earned 168 total points
ID: 22657118
INSERT INTO TableName
(COL1, COL2) values
(VAL1, VAL2)

-- Error Handler
IF @@ERROR <> 0
BEGIN
PRINT "Error occurred "
Return
END
ELSE
BEGIN
PRINT "Success..!!"
RETURN(0)
END
GO


Check this link also
http://www.sommarskog.se/error-handling-II.html
0
 

Author Comment

by:pavanark
ID: 22657133
using raiserror()
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 336 total points
ID: 22657143
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @@MIN_LVL tinyint,
   @@MAX_LVL tinyint,
   @@EMP_LVL tinyint,
   @@JOB_ID smallint
SELECT @@MIN_LVl = min_lvl,
   @@MAX_LV = max_lvl,
   @@ EMP_LVL = i.job_lvl,
   @@JOB_ID = i.job_id
FROM employee e, jobs j, inserted i
WHERE e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@@JOB_ID = 1) and (@@EMP_lVl <> 10)
BEGIN
   RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
   ROLLBACK TRANSACTION
END
ELSE
IF NOT @@ EMP_LVL BETWEEN @@MIN_LVL AND @@MAX_LVL)
BEGIN
   RAISERROR ('The level for job_id:%d should be between %d and %d.',
      16, 1, @@JOB_ID, @@MIN_LVL, @@MAX_LVL)
   ROLLBACK TRANSACTION
END

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query resolving a string conversion issue 26 37
Get Duration of last Status Update 4 30
SQL Server - Slabs 9 37
Add '#' to end of file 2 29
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now