CODEWITHSUNDEEP
sql-serveriterationsql-delete
Alex
Alex

Reputation: 167

How iteration names of tables and delete records from them?

I am trying to create a procedure in which I create a variable -> @table_name and assign a select, that gets a list of tables names from sys.table.

Then I need to insert the @table_name to line with delete and go into each table from the list and delete records in them.

I will be grateful for help.

create procedure Test
as
    declare @table_name = (select [name] 
                           from sys.tables  
                           where [name] like ('%x1') or [name] like ('%x2')) 

    -- how I can do the iteration???    
    delete [@table_name] 
    where id in (select id 
                 from [@table_name]  
                 where column_2 like ('%.%'))

Upvotes: 0

Views: 394

Answers (1)

T N
T N

Reputation: 9907

I believe you need to use a cursor to loop through the selected table names and then construct and execute dynamic SQL. Something like:

declare @name sysname
declare @sql nvarchar(1000)

declare table_cursor cursor for
select name
from sys.tables
where name like '%x1' or name like '%x2'

open table_cursor
fetch next from table_cursor into @name

while @@fetch_status = 0
begin
    set @sql = 'delete from ' + quotename(@name) + ' where column_2 like ''%.%'''
    exec (@sql)

    fetch next from table_cursor into @name
end

close table_cursor
deallocate table_cursor

I simplified the delete statement, but you may need to make additional changes to suit your specific needs. Note that because the '%.%' literal is itself contained withing another literal, the quotes are doubled up.

Upvotes: 2

Related Questions