Can some one help me change the cursor to a while loop or use a temp table.
Sp is below.
alter Procedure [dbo].[spDeleteRows]
(
@scname varchar(50),
@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 */
@cChildCriteria1 nvarchar(1000),
@cChildCriteria3 nvarchar(1000) ,/*records from the child table /
@iChildRows int / number of rows deleted from the child table */
DECLARE cFKey CURSOR LOCAL FOR
SELECT
t_parent.name AS TAB
, c_parent.name AS COL
, t_child.name AS RefTab
, c_child.name AS RefCol
,fk.name AS FKName
,sch.name as schema_name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables t_parent
ON t_parent.object_id = fk.parent_object_id
INNER JOIN sys.columns c_parent
ON fkc.parent_column_id = c_parent.column_id
AND c_parent.object_id = t_parent.object_id
INNER JOIN sys.tables t_child
ON t_child.object_id = fk.referenced_object_id
INNER JOIN sys.columns c_child ON c_child.object_id = t_child.object_id
AND fkc.referenced_column_id = c_child.column_id
INNER JOIN sys.schemas sch ON sch.schema_id = t_child.schema_id
AND t_child.NAME=@cTableName
and sch.name=@scname
OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName,@scname
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @cChildCriteria1 = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
-- @cRefTab +'] WHERE ' + @cCriteria + ')'
SET @cChildCriteria =
' join ' + @cRefTab + ' on ' +@cRefTab+'.'+@cRefCol+'='+@cTab+'.'+@cCol +' '+ @cCriteria
--print 'Deleting records from table ' + @cTab
EXEC spDeleteRows @scname,@cTab, @cChildCriteria, @iChildRows OUTPUT
--EXEC spDeleteRows @scname,@cTab, @cChildCriteria1, @iChildRows OUTPUT
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName,@scname
END
Close cFKey
DeAllocate cFKey
SET @cSQL = 'DELETE ' + @cTableName + ' FROM [' + @cTableName + '] ' +@cCriteria
print @cSQL
--EXEC sp_ExecuteSQL @cSQL
--print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName