Transact SQL help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transact SQL help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-07 : 09:59:29

Kevin writes "I am trying to create a stored procedure that will delete old data from many tables in my database.

I have one table called NameTable with a list of table names that exist in the database.

I was trying to delete old data from each of the tables by using this type of statement:

Declare @tableName varchar

DECLARE NameTable_Cursor CURSOR FOR
SELECT name FROM NameTable

OPEN NameTable_Cursor

-- Perform the first fetch.
FETCH NEXT FROM NameTable_Cursor INTO @tableName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
select * from @tablename
FETCH NEXT FROM NameTable_Cursor INTO @tableName
END

CLOSE NameTable_Cursor
DEALLOCATE NameTable_Cursor
GO

I was doint a select * from @tablename just for testing pruposes.
HOWEVER I get an error on the select statement saying that I must declare the @tablename variable.

How can I accomplish this?"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-07 : 10:12:52

You need to use dynamic sql.

Something like this:
DECLARE @sql nvarchar(300)
Declare @tableName varchar
...
SELECT @sql = 'select * from' + @tablename
exec sp_executesql @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources