Generate Delete Stored Procedure

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ecm_GENERATE_DELETE_PROC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ecm_GENERATE_DELETE_PROC]
GO







CREATE                PROCEDURE ecm_GENERATE_DELETE_PROC   
@table_name nvarchar(128),  /*TABLE NAME TO DELETE FROM*/@column_name nvarchar(128),  /*COLUMN NAME THAT IS THE KEY*/@new_proc_name nvarchar(128) /*THE NAME OF THE NEW PROCEDURE*/
   
AS   
   
SET NOCOUNT ON   
   
DECLARE   
@current_table nvarchar(128),   
@current_column nvarchar(128),  
@del_proc1 varchar(8000),  
@rowid int,   
@set_warning bit,   
@warning nvarchar(255)  
   
BEGIN  

--error checking
IF @table_name NOT IN
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.Tables)
BEGIN ---invalid table name
PRINT 'ERROR: NO TABLE NAMED: ' + '''' + @table_name + '''' + ' IN DATABASE: ' + '''' + db_name() + ''''
RETURN
END ---invalid table name

IF @column_name NOT IN
(SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @table_name)
BEGIN ---invalid column name
PRINT 'ERROR: NO COLUMN NAMED: ' + '''' + @column_name + '''' +  ' IN TABLE: ' + '''' + @table_name + '''' + ' IN DATABASE: ' + '''' + db_name() + ''''
RETURN
END ---invalid column name
  
CREATE TABLE #tmp_del_builder  
(rowid int identity(1,1),   
ref_table nvarchar(128),   
ref_column nvarchar(128),   
child_table nvarchar(128),   
child_column nvarchar(128),   
used bit)  
  
INSERT INTO #tmp_del_builder  
(ref_table, 
ref_column, 
child_table, 
child_column, 
used)  
SELECT    
@table_name as 'Referenced Table',    
f.name as 'Referenced Column',   
c.name as 'Tables That Reference',    
e.name as 'Referencing Column',   
0  
FROM sysobjects a    
JOIN sysforeignkeys b   
on a.id = b.rkeyid    
JOIN sysobjects c   
on c.id = b.fkeyid   
JOIN sysobjects d   
on d.id = b.constid   
JOIN syscolumns f   
on f.id = a.id   
AND b.rkey = f.colid   
JOIN syscolumns e   
on c.id = e.id    
AND b.fkey = e.colid   
WHERE a.name = @table_name    
ORDER BY c.name   
  
SELECT @del_proc1 =  
' CREATE PROCEDURE ' + @new_proc_name + char(13) + char(10) +
   ' @' + @column_name + ' varchar(128) ' + char(13) + char(10) + ' AS ' + char(13) + char(10) + 
'SET NOCOUNT ON ' + char(13) + char(10) + ' BEGIN ' + char(13) + char(10) +
' BEGIN TRANSACTION ' + char(13)  + char(10)

--print out the create procedure block
SELECT @del_proc1

SET @del_proc1 = ''
WHILE EXISTS   
(SELECT * 
FROM #tmp_del_builder 
WHERE used = 0)  
BEGIN --- loop  
SELECT @set_warning = 0  
  
SELECT TOP 1   
@current_table = child_table,   
@current_column = child_column,   
@rowid = rowid  
FROM   
#tmp_del_builder  
WHERE   
used = 0  
  
IF OBJECT_ID(@current_table) IN   
(SELECT [rkeyid] FROM sysforeignkeys)  
BEGIN---warn of other foreign key   
SELECT @warning = ' /***WARNING: Table ' + @current_table + ' has other foreign key constraints.***/ '  
SELECT @set_warning = 1  
END  
  
SELECT @del_proc1 =    
' DELETE FROM ' + @current_table + char(13) + char(10) + 
' WHERE ' + @current_column + ' = @' + @column_name + char(13) + char(10) +  
' IF @@ERROR! = 0 ' + char(13) + char(10) +'  ROLLBACK ' + char(13)  + char(10)
--if possible other constraints on deleted columns, then print warning.
IF @set_warning = 1  
SELECT @del_proc1 = @del_proc1 + @warning + char(13)  + char(10) + char(13) + char(10)
ELSE  
SELECT @del_proc1 = @del_proc1 + char(13)  + char(10)
  
UPDATE #tmp_del_builder  
SET used = 1   
 WHERE rowid = @rowid  
  
--print out the foreign key delete blocks
SELECT @del_proc1

END --- loop  
  
SELECT @del_proc1 = 
' DELETE FROM ' + @table_name + 
' WHERE ' + @column_name + ' = @' + @column_name + char(13) + char(10) +char(13) + char(10) + 
'IF @@ERROR! = 0 ' + char(13) + char(10) +'ROLLBACK ' + char(13) + char(10) +'ELSE ' + char(13) + char(10) +
'COMMIT ' + char(13) + char(10) +'END'  
--print out the last delete statement and the commit to close the procedure
SELECT @del_proc1

END 
   




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO