What's up!

Pyaarey Allah!

Friday, July 29, 2011

Cascading and Bulk Delete through one stored procedure

0 comments

Create PROCEDURE CascadeDelete
@table varchar(100), -- Table name
@column varchar(100), -- Primary key column name
@value varchar(max) -- Must be comma separated list of primary keys 
AS

Print 'Need to delete from ' + @table + ' where ' + @column 
+ ' IN (' + convert(varchar, @value) + ')'

DECLARE @refrencingTable varchar(100)
DECLARE @refrencingColumn varchar(100)
DECLARE @refrencingTablesPrimaryKeyColumn varchar(100)

DECLARE @sql varchar(4000)
DECLARE @keyval bigint

BEGIN TRY
SET @sql = 'delete from ' + @table + ' where ' + @column 
+ ' IN (' + convert(varchar, @value) + ')'
EXEC (@sql)
PRINT 'Bulk delete worked' 
-- Deleting was successful becuase the table does not seem to refer anything
END TRY
BEGIN CATCH
-- first, find all the objects which refer to this object
DECLARE ref CURSOR LOCAL FOR -- ref is a cursor to find references
SELECT DISTINCT
OBJECT_NAME(f.parent_object_id) AS table_name
, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
, sc.name as table_key
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
JOIN sys.columns as sc
ON f.parent_object_id = sc.object_id
JOIN sys.indexes as i
on f.parent_object_id = i.object_id
AND i.is_primary_key = 1
JOIN sys.index_columns as ic
on i.index_id = ic.index_id
AND i.object_id = ic.object_id
AND i.is_primary_key = 1
AND sc.column_id = ic.column_id
WHERE f.referenced_object_id = OBJECT_ID(@table);

-- loop over the referring objects
OPEN ref
FETCH NEXT FROM ref INTO @refrencingTable, @refrencingColumn, 
@refrencingTablesPrimaryKeyColumn
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC cascadeDelete @refrencingTable, @refrencingColumn, @value 
-- Self call to delete references
print @table + ' is referenced by ' + @refrencingTable + '.' + @refrencingColumn

-- get all the id values for all the referring records and put them into a temp table
SET @sql = 'SELECT ' + @refrencingTablesPrimaryKeyColumn 
+ ' as keyval FROM ' + @refrencingTable + ' WHERE ' + @refrencingColumn 
+ ' IN (' + CONVERT(varchar, @value) + ')'

CREATE TABLE #temp (
keyval int
)

INSERT INTO #temp
EXEC (@sql)

-- loop over the table and for each row, use cascase delete to delete it.
DECLARE del CURSOR LOCAL FOR
SELECT keyval FROM #temp

OPEN del
FETCH NEXT FROM del INTO @keyval
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC cascadeDelete @refrencingTable, @refrencingTablesPrimaryKeyColumn, @keyval

FETCH NEXT FROM del INTO @keyval
END
CLOSE del
DEALLOCATE del

DROP TABLE #temp

FETCH NEXT FROM ref INTO @refrencingTable, @refrencingColumn, 
@refrencingTablesPrimaryKeyColumn
END
CLOSE ref
DEALLOCATE ref

SET @sql = 'DELETE FROM ' + @table + ' WHERE ' + @column 
+ ' IN (' + CONVERT(varchar, @value) + ')'
PRINT @sql
EXEC (@sql)
END CATCH

Friday, July 15, 2011