I am using T-SQL to fetch some table names which satisfy a certain criteria - put the name of those tables in a temporary table - @MyTempTable
and plan to delete all rows from all tables which have an entry in @MyTempTable
.
I notice that my @MyTempTable
is populated correctly with the names of the relevant tables that have to be deleted, but when I try to execute the DELETE
command on each entry in @MyTempTable
, it throws a syntax error saying "Must declare a table variable "@localVar
". I guess this is because it is not letting me use the value of each row as the name of the table to delete entries from. Can you suggest how I can fix this?
/* Define temporary table */
DECLARE @MyTmpTable TABLE ( my_row varchar(20));
/* local variable holds the name of the table that has to be deleted based on the query below */
DECLARE @localVar varchar(20);
/* Declare curser */
DECLARE c5 CURSOR FOR (
/* SELECT TOP 10 vc_merchant_name FROM dbo.merchant_versioned */
select
t.name
from
sys.tables t
where
exists (
select null from sys.columns c
where c.object_id = t.object_id and c.name = 'i_version_id')
);
OPEN c5;
FETCH NEXT FROM c5 INTO @localVar;
WHILE @@FETCH_STATUS = 0
BEGIN
/*************************************************/
DELETE FROM @localVar; /* <<<--- THIS DOESN"T WORK
/*************************************************/
FETCH NEXT FROM c5 INTO @localVar;
END
CLOSE c5;
DEALLOCATE c5;