April 7, 2015 at 6:36 am
Hi Experts,
I have taken all the user tables in a database in a cursor. I need to fetch each table and deletes some rows based on a common condition (like ID=50) with in a transaction. It delete succeed transaction should be committed else transaction should be rolled back for the tables and table name needs to be inserted to a log table and the process should continue with the next table.
Please do help me to construct the logic.
Thanks in advance.
Naveen J V
April 7, 2015 at 6:42 am
so, if your cursor were going to loop through 50 tables,
and you get an error on table #30, do you want to rollback just table30, or the 29 previous statements too?
why not create a stored procedure, without dynamic sql, that uses a parameter that actually does the deletes for the fifty tables, taking into consideration any foreign keys or other logic required on a per table basis? once set up, it reusable, and easier to edit than a cursor.
Lowell
April 7, 2015 at 6:44 am
Rollback only table 30 and loop should continue from 31 onwards
April 7, 2015 at 7:00 am
SP is also fine, but again SP needs to be called for each table right?
and transaction should be handled with in SP or what?
April 7, 2015 at 7:03 am
that would be the default action for dynamic sql within a cursor, then. just don't wrap the cursor in a transaction.
i would not use a cursor for this, but pending better information, here is a model to look at; untested, but syntactically correct.
DECLARE @FailedCommands TABLE(ID int identity(1,1), cmd varchar(8000))
DECLARE
@isql VARCHAR(2000),
@ObjectName VARCHAR(128),
@ColumnName VARCHAR(128),
@Value int = 50
DECLARE c1 CURSOR FOR SELECT
quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id)) As ObjectName,
quotename(name) as ColumnName
FROM sys.columns
WHERE object_name(object_id) LIKE'%invoice%'
and name ='ID' --some critieria to decide the right table/column?
and object_schema_name(object_id) <> 'sys'
OPEN c1
FETCH NEXT FROM c1 INTO @ObjectName,@ColumnName
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql = 'DELETE FROM ' + @ObjectName + ' WHERE ' + @ColumnName + ' = ' + convert(varchar,@Value) + ';'
PRINT @isql
EXEC(@isql)
if @@error <> 0
BEGIN
INSERT INTO @FailedCommands(cmd) SELECT @isql
END
FETCH NEXT FROM c1 INTO @ObjectName,@ColumnName
END
CLOSE c1
DEALLOCATE c1
Lowell
April 7, 2015 at 7:07 am
Naveen J V (4/7/2015)
SP is also fine, but again SP needs to be called for each table right?and transaction should be handled with in SP or what?
the stored proc can have 30 discrete commands in it, is what i mean.
so it might look something like this: deleting data in a specific order, and only if it meets specific criteria
DELETE FROM [dbo].[PatientNotes]WHERE [PatientID] = @Value
DELETE FROM [dbo].[PatientHistory]WHERE [PatientID] = @Value
DELETE FROM [dbo].[PatientSchedules]WHERE [PatientID] = @Value
DELETE FROM [dbo].[PatientDiagnosis]WHERE [PatientID] = @Value AND Status=42
DELETE FROM [dbo].[Patient]WHERE [PatientID] = @Value AND NOT EXISTS(SELECT * FROM [dbo].[PatientDiagnosis]WHERE [PatientID] = @Value AND Status=42)
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply