Deleting with a c ursor

• Jaime E. Maccou

  Ten Centuries

  Points: 1339

  More actions

  August 5, 2010 at 9:31 am

  #222129

  sorry to post this twice

  This a the basic statement

  USE master;

  GO

  SET NOCOUNT ON;

  GO

  DECLARE AllDatabases CURSOR FOR

  SELECT name FROM sys.databases WHERE database_id > 4

  OPEN AllDatabases

  DECLARE @DBNameVar NVARCHAR(128)

  DECLARE @Statement NVARCHAR(600)

  DECLARE @ADATE DATETIME

  FETCH NEXT FROM AllDatabases INTO @DBNameVar

  WHILE (@@FETCH_STATUS = 0)

  BEGIN

  SET @ADATE =(DATEADD(d,-60,getdate()))

  PRINT N'CHECKING DATABASE ' + @DBNameVar

  SET @Statement=N'DELETE FROM WORKLISTLOG WHERE CREATIONTIME <= @ADATE'

  EXEC sp_executesql @Statement

  PRINT CHAR(13) + CHAR(13)

  FETCH NEXT FROM AllDatabases INTO @DBNameVar

  END

  CLOSE AllDatabases

  DEALLOCATE AllDatabases

  GO

  SET NOCOUNT OFF;

  GO

  This is the error:

  CHECKING DATABASE ReportServer

  Msg 137, Level 15, State 2, Line 1

  Must declare the scalar variable "@ADATE".

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  August 5, 2010 at 9:34 am

  #1202861

  Please do not double post......

  continue this thread here

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden