I have this sp to delete child from child tables when something deleted from the main table .

Now I want the same function with update statement . When I update the id field on main table . I want to update all child table ids referring the main table id. Can someone help me with this .

CREATE Procedure spDeleteRows

    @cTableName varchar(50), / name of the table where rows are to be deleted /
    @cCriteria nvarchar(1000), / criteria used to delete the rows required /
    @iRowsAffected int OUTPUT / number of records affected by the delete /
set nocount on
declare     @cTab varchar(255), / name of the child table /
    @cCol varchar(255), / name of the linking field on the child table /
    @cRefTab varchar(255), / name of the parent table /
    @cRefCol varchar(255), / name of the linking field in the parent table /
    @cFKName varchar(255), / name of the foreign key /
    @cSQL nvarchar(1000), / query string passed to the sp_ExecuteSQL procedure /
    @cChildCriteria nvarchar(1000), /* criteria to be used to delete 
                                           records from the child table */
    @iChildRows int / number of rows deleted from the child table /

/ declare the cursor containing the foreign key constraint information /
SELECT SO1.name AS Tab, 
       SC1.name AS Col, 
       SO2.name AS RefTab, 
       SC2.name AS RefCol, 
       FO.name AS FKName
FROM dbo.sysforeignkeys FK  
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
                              AND FK.fkey = SC1.colid 
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
                              AND FK.rkey = SC2.colid 
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
    /* build the criteria to delete rows from the child table. As it uses the 
           criteria passed to this procedure, it gets progressively larger with 
           recursive calls */
    SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 
                              @cRefTab +'] WHERE ' + @cCriteria + ')'
    print 'Deleting records from table ' + @cTab
    / call this procedure to delete the child rows /
    EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
    FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
Close cFKey
DeAllocate cFKey
/ finally delete the rows from this table and display the rows affected  /
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.