How can I loop through SQL tables?

Question

I'm not convinced my title accurately represents my problem, but here is what I have:

DECLARE @ListOfTables TABLE(
    RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , TableName VARCHAR(200)
)

DECLARE @RowsToProcess INT
DECLARE @CurrentRow INT
DECLARE @TableName VARCHAR(200)


INSERT INTO @ListOfTables (TableName)
VALUES ('Books')
INSERT INTO @ListOfTables (TableName)
VALUES ('Magazines')
INSERT INTO @ListOfTables (TableName)
VALUES ('Essays')

SET @RowsToProcess = @@ROWCOUNT 
SET @CurrentRow = 0

I want to then loop through this table and delete.

WHILE @CurrentRow < @RowsToProcess
BEGIN 
    SET @CurrentRow = @CurrentRow+1

    SELECT @TableName = TableName
    FROM @ListOfTables
    WHERE RowID = @CurrentRow

    DELETE FROM @TableName

END

In actuality, my delete isn't just deleting the table - it has specific criteria. I've simplified it for demonstration purposes.

The error I get here is: Must declare the variable '@TableName'.

I've moved it into the WHILE loop and I receive the same error.

What can I do here? Is it possible to do what I'm trying to do?

Tab Alleman · Accepted Answer · 2014-11-06 21:48:15Z

1

Assuming you actually have tables in your db named books, magazines, and essays, and you want to delete from them, or do some query on them, you need to use dynamic sql. Not sure how it's done in MySQL, but in SQL-Server, it'd be like this (highly simplified example):

SET @sql = 'DELETE FROM ' + @TableName;
EXEC (@sql);

answered Nov 6, 2014 at 21:48

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQLburn Over a year ago

Just be careful where you are getting the data for the @TableName. You could open yourself to a sql injection.

Collectives™ on Stack Overflow

How can I loop through SQL tables?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related