Error while executing cursors

Question

Post reply

Error while executing cursors

Dj463

Ten Centuries

Points: 1181
More actions
January 17, 2011 at 5:38 pm

#230556

Hello all ,
I wrote a cursor that will loop through all the tables in a database and delete records older than 2 months. Below is the query:
declare @table varchar(128)
declare @cmd varchar(500)
declare @date datetime
set @date = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)
declare tables cursor for
select table_name from information_schema.tables where table_type = 'base table' and table_schema like '%human%'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
set @cmd = 'delete from ''' + @table + '''where modifieddate <' + '''' + CONVERT(nvarchar(50), @date, 120) + ''''
print @cmd
execute (@cmd)
fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
When i run this query it prints the statement that is being executed correctly but it gives me the following error instead of deleting data from tables:
delete from 'Tablename' where date <'2010-12-01 00:00:00'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Tablename'.
I tried using schema name before table name but the result is same. Could you please provide me with your suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Remove the single quote encapsulation from the table name. Use square brackets, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Dj463 Ten Centuries Points: 1181 More actions · Answer 2

Thank you jeff,

After using the square brackets it worked well.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

You bet. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions