sqlsql-serversql-server-2008azure-sql-databasecursors

Delete multiple records from table through cursor in sql server


there are number of test IP's which I would like to remove through system defined sp

exec sp_delete_firewall_rule from sys.firewall_rules table in sql server

I am using below cursor but its not working

declare @name nvarchar(max)

declare cur CURSOR LOCAL for

select @name from sys.firewall_rules where [name] like '%TestIP%'

open cur

fetch next from cur into @name

while @@FETCH_STATUS = 0 BEGIN

    exec sp_delete_firewall_rule  @name

    fetch next from cur into @name

END

close cur

deallocate cur

Solution

  • It worked for me, you just need to change a couple of things in your code.

    1. In the select list include the table ColumnName [name] instead of variable. You did not pass any value to the variable so this gives a NULL result.
    2. Include SP parameter while executing exec sp_delete_firewall_rule @name = @name1;

    I have these IP’s in my firewall rules:

    enter image description here

    With the below code I am deleting the IP’s which has a name like TestIP1.

    DECLARE @name1 nvarchar(128);
    DECLARE MyCursor CURSOR FOR
      SELECT [name] from sys.firewall_rules where [name] like '%TestIP1%';
    OPEN MyCursor;
    FETCH FROM MyCursor into @name1
      WHILE @@FETCH_STATUS = 0 BEGIN
        EXEC sp_delete_firewall_rule @name = @name1 ;
    FETCH next from MyCursor into @name1
    END
    CLOSE MyCursor;
    DEALLOCATE MyCursor;
    GO
    

    enter image description here

    Now the result shows only 1 IP which is not included in the above delete list.

    enter image description here