cursor error

[newtosql]

Im using SQL 7.0 and deleting a rows with cursor from user tables. If productID in exists in one of my ProductHistory tables and if the productID in the master table "Products" is less than a certain date then I want to delete it. I get an error msg (follows):

Server: Msg 137, Level 15, State 2, Line 14
Must declare the variable '@tablename.

Any ideas?

--------------------------------------------------------------------------------------------------------------
DECLARE @tablename sysname

DECLARE cur CURSOR FOR

Select [name] from sysobjects where xtype = 'U' and [name] like 'ProdHis%'

OPEN cur
FETCH NEXT FROM cur INTO @tablename
WHILE @@FETCH_STATUS = 0

BEGIN

delete from @tablename
where ProductID in (select ProductID from Products
where (ShelfLife <= CONVERT(DATETIME,'2005-03-31 00:00:00', 102)))

FETCH NEXT FROM cur INTO @tablename
END
CLOSE cur
DEALLOCATE cur

 

[micha123]

delete from @tablename ...
is not a valid syntax.
You must substitute it with the following:

SET @command = 'delete from ' + @tablename +
    ' where ProductID in (select ProductID from Products
    where (ShelfLife <= CONVERT(DATETIME, '2005-03-31 00:00:00'', 102)))'

EXEC (@command)

-- AND DON'T FORGET TO DECLARE @command BEFORE! :
DECLARE @command varchar(1000)

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[JohnDTampaBay]

Object names cannot be referenced by a variable. You will need dynamic SQL. CONVERT styles for dates only apply when converting to char or varchar. Was that a typo? Change the guts of the cursor like so:

DECLARE @sql varchar(2000)
BEGIN
  SET @sql =
     ' DELETE FROM ' + @TableName +
     ' WHERE ProductID IN (SELECT ProductID ' +
     '                     WHERE ShelfLife <= ''2005-03-31''))'

  EXEC(@sql)
  FETCH NEXT FROM cur INTO @tablename
END

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)