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)');

• Subin Benny: any alternatives for using the string_agg function ? It won't support in my version .
• Charlieface: See new edits..
• Subin Benny: can you do one more favor . How do i update it instead of deleting .Like if i want to update an id field of the main table then all the child table reference also get updated, is that possible???
• Charlieface: Pretty much impossible, as you need the primary key to exist before you can foreign key it. Perhaps you could do it by duplicating the primary parent rows first, changing the foreign keys, then deleting the old rows. But it's generally not recommended to change primary keys anyway, for a multitude of reasons.
• Subin Benny: dba.stackexchange.com/questions/337688/… @Charlieface could u please look at this ?