[SOLVED] SQL Query to delete data from tables defined in table

SQL Query to delete data from tables defined in table

I'm trying to delete data from a number of tables in my SQL base.

In the database I have a table called company which contains the names of each table that I need to delete data from.

lets assume that I have 3 companies in my company table.

What I want to do is delete all records in some certain tables in each company.

So, in the company table I have the following 3 records:

1  2  3

There are also the following tables in the database which depicts each company's scanned documents.

dbo.1.documents
dbo.2.documents
dbo.3.documents

What I am trying to do is to create a SQL query that will run through the dbo.company table and clear the document tables based on the company names found there.

This is my code:

DECLARE @MyCursor CURSOR;
DECLARE @MyField varchar;
BEGIN
SET @MyCursor = CURSOR FOR
select top 1000 [Name] from dbo.Company    

OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @MyField

WHILE @@FETCH_STATUS = 0
BEGIN

  delete * from 'dbo.'+@MyField+'$documents'

  FETCH NEXT FROM @MyCursor 
  INTO @MyField 
END; 

CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;

I am not sure how the syntax should go but I imagine it is something like this.

Anybody care to chip in on how I can dynamically delete the data based on the records in the dbo.company.name?

Solution

Use dynamic sql.

Replace the delete-statement with code below (the declare can be done at the start):

DECLARE @sql NVARCHAR(1000)
SET @sql = N'delete from dbo.'+CONVERT(VARCHAR,@MyField)+'documents'
EXEC sp_executesql @sql