How to use temp table or while loop instead of cursor

Question

i have an sp to delete data from grandchildren tables .

But I need to change the cursor and use a temp table or while loop instead of the cursor. I have tried but was not working can somebody help.

Sp added below

CREATE Procedure spDeleteRows
/* 
Recursive row delete procedure.
It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on.  This is designed to do the 
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
table to find any child tables, then deletes the soon-to-be orphan records from them using 
recursive calls to this procedure. Once all child records are gone, the rows are deleted 
from the selected table.   It is designed at this time to be run at the command line. It could 
also be used in code, but the printed output will not be available.
/
    (
    @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 /
    )
As
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 /
DECLARE cFKey CURSOR LOCAL FOR 
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
OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
     BEGIN
    /* 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
     END
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

Answer 1

I must say, I would generally recommend that you either use cascading foreign keys, or write the code manually.

Be that as it may, you don't need a cursor, or a WHILE loop. You can build your queries using a recursive CTE, which gets each foreign key relationship and constructs a dynamic CTE of it, then simply deletes from each in order.

Note that my procedure does not take into account self-referencing foreign keys (you would need a dynamic recursive CTE for that) nor does it deal with multiple cascade paths. I leave that as an exercise for the reader.

You need a TVF for the join columns, as you cannot have aggregation inside a recursive CTE.

CREATE OR ALTER FUNCTION dbo.GetJoinCols (@parent_object_id int, @child_object_id int, @fk_object_id int)
RETURNS TABLE
AS RETURN
SELECT
  JoinCols = 
    STRING_AGG(
      CAST(
        'c.' + QUOTENAME(cChild.name) + ' = p.' + QUOTENAME(cParent.name)
        AS nvarchar(max)
      ),
      ' AND '
    )
FROM sys.foreign_key_columns fkc
JOIN sys.columns cParent ON cParent.object_id = @parent_object_id AND cParent.column_id = fkc.referenced_column_id
JOIN sys.columns cChild ON cChild.object_id = @child_object_id AND cChild.column_id = fkc.parent_column_id
WHERE fkc.constraint_object_id = @fk_object_id;

And the final procedure is

CREATE OR ALTER PROC DeleteWithFK
    @tableName sysname, -- name of the table where rows are to be deleted
    @Criteria nvarchar(1000), -- criteria used to delete the rows required
    @RowsAffected int = NULL OUTPUT, -- number of records affected by the delete /
    @schemaName sysname = 'dbo'
As
SET NOCOUNT ON;
DECLARE @sql nvarchar(max);
WITH cte AS (
    SELECT
      Level = 0,
      t.object_id,
      TableToDelete = QUOTENAME('cte' + t.name),  --must be the CTE name from below
      TablesAsCte = CONCAT(
        QUOTENAME('cte' + t.name),
        ' AS (
  SELECT *
  FROM ',
        QUOTENAME(s.name),
        '.',
        QUOTENAME(t.name),
        '
  WHERE ',
        CAST(@Criteria AS nvarchar(max)),
        '
)'
      )
    FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = @schemaName
      AND t.name = @tableName
UNION ALL

SELECT
  cte.Level + 1,
  tChild.object_id,
  QUOTENAME('cte' + tChild.name),  --must be the CTE name from below
  CONCAT(
    cte.TablesAsCte,
    ',

',
        QUOTENAME('cte' + tChild.name),
        ' AS (
  SELECT c.*
  FROM ',
        QUOTENAME(sChild.name),
        '.',
        QUOTENAME(tChild.name),
        ' c
  WHERE EXISTS (SELECT 1
      FROM ',
        cte.TableToDelete,
        ' p WHERE ',
        cols.JoinCols,
        '
    )
)'
      )
    FROM cte
    JOIN sys.foreign_keys fk ON fk.referenced_object_id = cte.object_id
    JOIN sys.tables tChild ON tChild.object_id = fk.parent_object_id
    JOIN sys.schemas sChild ON sChild.schema_id = tChild.schema_id
    CROSS APPLY dbo.GetJoinCols(cte.object_id, tChild.object_id, fk.object_id) cols
)
SELECT
  @sql = STRING_AGG(
    CONCAT(
      'WITH ',
      cte.TablesAsCte,
      '
DELETE ',
      cte.TableToDelete
    ),
    ';
'
  ) WITHIN GROUP (ORDER BY cte.Level DESC)
FROM cte;
SET @sql += ';
SET @RowsAffected = @@ROWCOUNT;
';
PRINT @sql;  -- your friend
EXEC sp_executesql @sql,
    N'@RowsAffected int OUTPUT',
    @RowsAffected = @RowsAffected OUTPUT;

db<>fiddle

The resulting SQL might look like this for example

WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE SomeColum = 'Somevalue'
),
[cteCUSTOMER] AS (
  SELECT c.*
  FROM [dbo].[CUSTOMER] c
  WHERE EXISTS (SELECT 1
      FROM [cteAGENTS] p WHERE c.[AGENT_CODE] = p.[CODE]
    )
),
[cteORDERS] AS (
  SELECT c.*
  FROM [dbo].[ORDERS] c
  WHERE EXISTS (SELECT 1
      FROM [cteCUSTOMER] p WHERE c.[CUST_CODE] = p.[CODE]
    )
)
DELETE [cteORDERS];
WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE 1=1
),
[cteCUSTOMER] AS (
  SELECT c.*
  FROM [dbo].[CUSTOMER] c
  WHERE EXISTS (SELECT 1
      FROM [cteAGENTS] p WHERE c.[AGENT_CODE] = p.[CODE]
    )
)
DELETE [cteCUSTOMER];
WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE 1=1
)
DELETE [cteAGENTS]
SET @RowsAffected = @@ROWCOUNT;

---

If STRING_AGG is not supported on your version of SQL Server, you would need to hack it with FOR XML

CREATE OR ALTER FUNCTION dbo.GetJoinCols (@parent_object_id int, @child_object_id int, @fk_object_id int)
RETURNS TABLE
AS RETURN
SELECT
  JoinCols = STUFF((
    SELECT
      ' AND c.' + QUOTENAME(cChild.name) + ' = p.' + QUOTENAME(cParent.name)
    FROM sys.foreign_key_columns fkc
    JOIN sys.columns cParent ON cParent.object_id = @parent_object_id AND cParent.column_id = fkc.referenced_column_id
    JOIN sys.columns cChild ON cChild.object_id = @child_object_id AND cChild.column_id = fkc.parent_column_id
    WHERE fkc.constraint_object_id = @fk_object_id
    FOR XML PATH(''), TYPE
  ).value('text()[1]', 'nvarchar(max)'),
  1,
  LEN(' AND '),
  ''
);

And

WITH cte AS (
.............
-- same as above
)
SELECT @sql = (
    SELECT
      CONCAT(
        'WITH ',
        cte.TablesAsCte,
        '
DELETE ',
        cte.TableToDelete,
    ';
'
      )
    FROM cte
    ORDER BY cte.Level DESC
    FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)');