August 19, 2008 at 5:09 am
Hi All,
I am trying to insert some error handling to the sql script below to delete duplicate records (using sql 2000 syntax), NOT "Try/Catch" methods in sql 2005.
Objectives
-------------------
1. The script produced should rollback the transaction if it detects any errors.
2. Script should also print out total number of rows affected and confirm that the rows are all deleted.
3. Script must check for execution errors
4. Script should be clearly commented to show syntax used
5. Error handling to be between sql statements "Begin Transaction" and "Commit Transaction".
Script to Delete duplicate ICS records
-----------------------------------------------------------------
SET NOCOUNT ON
SELECT Convert(varchar(20), @@servername)as 'server',
Convert(varchar(10), db_name()) as 'database',
Convert(varchar, Getdate(), 113) as 'date run'
SET ANSI_NULLS OFF
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER OFF
DECLARE @individual_id Int
DECLARE @ics_id Int
DECLARE @tag_id Int
DECLARE @taz_id Int
SET NOCOUNT ON
PRINT 'duplicate ICS records to be deleted:-'
PRINT ''
select * from ics j
where exists(select * from ics j2 where j2.individual_id = j.individual_id
and j2.ics_id <> j.ics_id)
order by j.individual_id
BEGIN TRANSACTION
Print 'Patching'
Print ''
DECLARE curs_1 INSENSITIVE CURSOR
FOR
select individual_id, ics_id
from ics j
where exists(select * from ics j2 where j2.individual_id = j.individual_id
and j2.ics_id <> j.ics_id)
OPEN curs_1
FETCH NEXT FROM curs_1
INTO @individual_id, @ics_id
WHILE @@fetch_status = 0
BEGIN
IF exists(select * from ics where ics_id <> @ics_id and individual_id = @individual_id)
BEGIN
delete ics where ics_id = @ics_id
delete ics_track_charging where ics_id = @ics_id
update dt
SET dt.ics_track_charging_id = NULL
FROM ics_track_charging jde
JOIN student_binn dt
ON dt.ics_track_charging_id = jde.ics_track_charging_id
where jde.ics_id = @ics_id
IF (select count(*) from ics where individual_id = @individual_id) = 1
BEGIN
IF (select directed_by_taz_model from ics where individual_id = @individual_id) = 1
BEGIN
SELECT top 1 @tag_id = ldk.tag_id, @taz_id = ldk.taz_id
from student_tagz_record ldk
where individual_id = @individual_id
and ldk.is_ready = 1
and ldk.distribution_time = (select MAX(ldk4.distribution_time)
FROM student_tagz_record ldk4
WHERE ldk4.individual_id = ldk.individual_id) AND
ldk.time_recent_change < (select max(ldk5.time_recent_change)
FROM student_tagz_record ldk5
WHERE ldk5.individual_id = ldk.individual_id
and ldk5.distribution_time = ldk.distribution_time)
EXEC ce_Rebuild_ICS_Track_Charging @individual_id, @tag_id, @taz_id
END
END
END
FETCH NEXT FROM curs_1
INTO @individual_id, @ics_id
END
CLOSE curs_1
DEALLOCATE curs_1
SET NOCOUNT OFF
Print 'Success'
--COMMIT TRANSACTION
ROLLBACK TRANSACTION
Thanks in advance
August 19, 2008 at 5:34 am
some of the ways to handle the errors in sql2000.
1) Print
2) Raiseerror
3) @@error
@@error - Global variable - is used to capture the status of last executed statuement. if it is not equal to 0 ,then you can do your ROLLBACK opeartions.
@@rowcount - global variable - is used to return the number of rows affected by the last statement.
If you don't know about it, please refer the BOL.
karthik
August 19, 2008 at 10:14 am
@@Error is what you're looking for.
One thing to be careful of is that @@Error contains the status of the last statement. Every single statement within SQL (including things like IF) will set @@Error to their return code. 0 if they succeeded otherwise the error returned.
Because of this, it's important to capture the value of @@Error after each statement and then to examine the captured value.
I wrote a little about the usage of @@error here:
http://sqlinthewild.co.za/index.php/2008/05/20/common-t-sql-mistakes/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply