question

Try This IF (OBJECT_ID('dbo.IB_delCleanupHistory') IS NOT NULL) BEGIN DROP PROCEDURE dbo.IB_delCleanupHistory END GO IF (OBJECT_ID('dbo.IB_delCleanupHistoryForDEVTables') IS NOT NULL) BEGIN DROP PROCEDURE dbo.IB_delCleanupHistoryForDEVTables END GO CREATE PROCEDURE dbo.IB_delCleanupHistory (@Table varchar(100), @IDField VARCHAR(100)) AS BEGIN DECLARE @srtStoreValue NVARCHAR(4000) DECLARE @dtYesterday DATETIME DECLARE @dtLast30 DATETIME DECLARE @dtLast90 DATETIME SELECT @dtYesterday = DATEADD(DAY, -1, GETDATE()) SELECT @dtLast30 = DATEADD(DAY, -30, GETDATE()) SELECT @dtLast90 = DATEADD(DAY, -90, GETDATE()) DECLARE @strSql NVARCHAR(4000) SET @strSql = ' DELETE ' + @Table + 'Hst --SELECT ' + @Table + 'Hst.' + @IDField + ', ' + @Table + 'Hst.CrtDat, FirstHst.crtdat FROM ' + @Table + 'Hst LEFT OUTER JOIN (SELECT ' + @IDField + ', MIN(CrtDat) AS crtdat FROM ' + @Table + 'Hst WHERE CrtDat > ''' + CONVERT(VARCHAR(20),@dtLast90) + ''' AND CrtDat <= ''' + CONVERT(VARCHAR(20),@dtLast30) + ''' GROUP BY ' + @IDField + ', CAST(CrtDat AS DATE)) AS FirstHst ON ' + @Table + 'Hst.' + @IDField + ' = firstHst.' + @IDField + ' AND ' + @Table + 'Hst.crtdat = firsthst.crtdat WHERE ' + @Table + 'Hst.CrtDat > ''' + CONVERT(VARCHAR(20),@dtLast90) + ''' AND ' + @Table + 'Hst.CrtDat <= ''' + CONVERT(VARCHAR(20),@dtLast30) + ''' AND ' + @Table + 'Hst.UpdDat IS NOT NULL AND FirstHst.crtdat IS NULL' print((CONVERT(DATETIME,@dtLast90))) SET @strSql = REPLACE(REPLACE(REPLACE(REPLACE(@strSQL, '$TAB', @Table), '$FLD', @IDField),'$LAST30', @dtLast30),'$dtLast90',CONVERT(VARCHAR(19),@dtLast90)) print(@dtLast90); print(@strSql); EXECUTE sp_executesql @strSql END GO CREATE PROCEDURE dbo.IB_delCleanupHistoryForDEVTables AS BEGIN EXEC dbo.IB_delCleanupHistory @Table = 'Documentation', @IDField = 'DocumentationId' END EXEC dbo.IB_delCleanupHistoryForDEVTables