Need help figuring out the cursor-CodePudding

Upd: Thanks everyone for your answers. I appreciate it a lot. Few points to add :

I replaced the actual package name to '%%' as i dont wan't to get in trouble with NDA related stuff, so i chose to be extra cautious.
I do use select @sql to see the query that is executed, but thanks for noticing that :)
Thanks again everyone for your answers.

this is my first post so it may be a little underwhelming :D

I wrote this script to help me delete data with all dependencies it has at once, but there are two issues i have :

declare @PackageId uniqueidentifier ;
set @PackageId = (select Id from SysPackage where name like '%%');
declare @tableName varchar(max);
declare @Columnname varchar(max);
declare @sql varchar(max);
declare dependent_tables Cursor 
FOR SELECT  
    tab1.name,
    col1.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
INNER JOIN sys.foreign_keys fk
    ON fk.name = obj.name
where tab2.name ='SysPackage'

Open dependent_tables

Fetch NEXT from dependent_tables INTO
    @tableName, 
    @columnName; 

WHILE @@Fetch_Status = 0 
Begin
    if @tableName is null Break;
    if @Columnname is null Break;
    set @sql=CONCAT('delete from ',@tableName,' WHERE ',@columnName,' = ',@PackageId); 
    exec (@sql); 
END;


Deallocate dependent_tables;

So there are basically two issues I have with this.

The loop is infinite, as the cursor doesn't actually loops through the records, but always stays on the same @tableName and @columName
There is also an issue with @packageId, which is being inserted into the query without the quotes, so the query looks like this :

delete from Table1 where column1 = A5664658-26D5-4600-862A-86467FD59244

I thought of creating an extra parameter to contact, kind of like this :

set @sql=CONCAT('delete from ',@tableName,' WHERE ',@columnName,' = ', ''',@PackageId, ''');

But it won't let me escape the '. Any ideas how to figure out the way to fix it would be much much appreciated.

Thanks.

CodePudding user response：

A few tips here. Firstly, use appropriate data types. An object's name can't be up for 2 Billion ANSI characters; it's name is stored as a sysname (a synonym for nvarchar(128) NOT NULL).

For your dynamic statement, don't inject parameters parametrise them. Don't inject raw object names either, delimit identify them (with QUOTENAME).

You should also always schema qualify your objects.

Also, I know you have a table called SysPackage. This is a bad idea; sys is a prefix already used by Microsoft. Have a read of Does prefixing system or general table names with "Sys" fine in Sql Server?.

I note, as well, that your subquery, (SELECT Id FROM dbo.SysPackage WHERE name LIKE '%%') is effectively WHERE Name IS NOT NULL. I also doubt that query returns a single value, so I suspect that is another problem you need to address.

All of this gives you something like this:

DECLARE @PackageId uniqueidentifier;
SET @PackageId = (SELECT Id FROM dbo.SysPackage WHERE name IS NOT NULL); -- LIKE '%%' is odd. Just check if the value isn't NULL
DECLARE @SchemaName sysname; 
DECLARE @TableName sysname; --Use an appropriate data type
DECLARE @ColumnName sysname;  --Use an appropriate data type
DECLARE @sql nvarchar(MAX); --SQL is an nvarchar, not a varchar
DECLARE dependent_tables CURSOR FOR
    SELECT sch.[name], --Ensure you schema qualify
           tab1.[name],
           col1.[name]
    FROM sys.foreign_key_columns fkc
         INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
         INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
         INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
         INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id
                                    AND col1.object_id = tab1.object_id
         INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
         INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id
                                    AND col2.object_id = tab2.object_id
         INNER JOIN sys.foreign_keys fk ON fk.name = obj.name
    WHERE tab2.name = 'SysPackage';

OPEN dependent_tables;

FETCH NEXT FROM dependent_tables
INTO @SchemaName,
     @tableName,
     @Columnname;

WHILE @@Fetch_Status = 0
BEGIN

    SET @sql = CONCAT(N'DELETE FROM ', QUOTENAME(@SchemaName), N'.', QUOTENAME(@TableName), N' WHERE ', QUOTENAME(@Columnname), N' = @PackageId');
    EXEC sys.sp_executesql @SQL, N'@PackageId uniqueidentifier', @PackageId;

    FETCH NEXT FROM dependent_tables
    INTO @SchemaName,
         @TableName,
         @Columnname;
END;


DEALLOCATE dependent_tables;
CLOSE dependent_tables;

CodePudding user response：

Just replace the following code. You forgot to fetch data within your WHILE loop.

WHILE @@Fetch_Status = 0 
Begin
    if @tableName is null Break;
    if @Columnname is null Break;
    set @sql=CONCAT('delete from ',@tableName,' WHERE ',@columnName,' = ',@PackageId); 
    exec (@sql); 

    FETCH NEXT FROM dependent_tables INTO 
    @tableName, 
    @columnName;  
END;