question

Hi, I have this stored procedure which was sent to me by a colleague. The stored procedure is supposed to automate table archiving in SQL Server. The problem I was having when reading the SP is that in the section where we assigned SET @CutOffSQL below; where @CutOffDate = The cut off date, generated by using DATEADD with the @CutOffInterval and @CutOffType parameters @CutOffSQL = N'SELECT @CutOffDate SET @CutOffSQL = N'SELECT @CutOffDate = CONVERT(varchar(20), DATEADD(' + @CutOffType + ', -ABS(' + CAST(@CutOffInterval AS nvarchar(10)) + '), GETDATE()),100)' This is the part I am getting confused. Can someone help check if there is an error with this part of the query. Below is the Stored Procedure and comments about the SP. ------------------------------------------------------------------------------------------------------------------------------------------------ Stored Procedure dbo.ArchiveData Description: This stored procedure dynamically creates a SQL statement to archive data from a production table to an archive table. This assumes that the structure of the source and destination tables are identical. The SQL statement is constructed by retrieving the column list for the tables from syscolumns (excluding computed columns). The columns are then appended into a single string via a cursor over the columns result set. This string isused to create a SQL statement in the following format: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION INSERT INTO () SELECT FROM WHERE < DELETE FROM WHERE < IF @@ERROR = 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK TRANSACTION END The generated SQL string is then executed via sp_executesql. The procedure requires the following input parameters: @SourceTable sysname - This is the table the data is archived from @DestinationTable sysname - This is the table where the data is archived to @CutOffInterval int - The number of days/months/years used to determine which records to archive @CutOffType varchar(4) - The type of value specified in the @CutOffInterval variable. This must be a valid date part value for the DATEADD funtion (yy,m,d, etc) @DateColumnName sysname - This is the name of the datetime column in the source table used to determine which records are archived. All records with a value in this column less than the datetime calculated by the @CutOffInterval and @CutOffType variables will be archived. @PrintOnly bit - Indicates the procedure should generate and print the SQL commands to archive but not actually execute them. Useful if you want to customize the commands. Note that the default on this parameter is 1, so explicitly set to 0, this proc will only print the SQL command, it will not execute it. */ CREATE PROCEDURE dbo.ArchiveData ( @SourceTable sysname, @DestinationTable sysname, @CutOffInterval int, @CutOffType varchar(4), @DateColumnName sysname, @PrintOnly bit = 1 ) AS DECLARE @SQL nvarchar(4000) -- The SQL commands that will be executes DECLARE @ColumnList varchar(4000) -- The list of columns in the source and destination tables DECLARE @Column sysname -- The current column in the cursor loop DECLARE @CRLF nvarchar(20) -- CRLF to make the generated SQL look pretty when it's printed DECLARE @CutOffSQL nvarchar(500) -- Holds a SQL command that generates the cut off date DECLARE @CutOffSQLParamList nvarchar(100) -- The parameter list passed into sp_executesql when generating the cut off date time DECLARE @CutOffDate nvarchar(20) -- The cut off date, generated by using DATEADD with the @CutOffInterval and @CutOffType parameters -- First we build a SQL string that when executed, will give us a datetime to use as the cut off, to determine which records are archived SET @CutOffSQL = N'SELECT @CutOffDate = CONVERT(varchar(20), DATEADD(' + @CutOffType + ', -ABS(' + CAST(@CutOffInterval AS nvarchar(10)) + '), GETDATE()),100)' SET @CutOffSQLParamList = N'@CutOffDate nvarchar(20) OUTPUT' EXEC sp_executesql @CutOffSQL, @CutOffSQLParamList, @CutOffDate OUTPUT SET @CRLF = CHAR(13) + CHAR(10) SET @ColumnList = '' -- Now we get a cursor of all columns in the source table, excluding computed colums DECLARE column_cursor CURSOR FAST_FORWARD FOR SELECT SC.name FROM syscolumns SC INNER JOIN sysobjects SO ON SC.id = SO.id WHERE ((SO.name = @SourceTable) AND (SC.iscomputed = 0)) ORDER BY SC.colorder OPEN column_cursor -- Next we loop through the cursor and create a list of columns that will be used in the INSERT and SELECT statements FETCH NEXT FROM column_cursor INTO @Column WHILE @@FETCH_STATUS = 0 BEGIN SET @ColumnList = @ColumnList + QUOTENAME(@Column) + ',' FETCH NEXT FROM column_cursor INTO @Column END CLOSE column_cursor DEALLOCATE column_cursor -- Clean up the trailing comma on the column list SET @ColumnList = SUBSTRING (@ColumnList,1,LEN(@ColumnList) - 1) -- And now we build the SQL commands, complete with transaction handling SET @SQL = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' + @CRLF SET @SQL = @SQL + 'BEGIN TRANSACTION' + @CRLF SET @SQL = @SQL + 'INSERT INTO ' + @DestinationTable + ' (' + @ColumnList + ')' + @CRLF SET @SQL = @SQL + 'SELECT ' + @ColumnList + @CRLF SET @SQL = @SQL + 'FROM ' + @SourceTable + @CRLF SET @SQL = @SQL + 'WHERE ' + @DateColumnName + ' < ''' + @CutOffDate + '''' + @CRLF SET @SQL = @SQL + 'DELETE FROM ' + @SourceTable + @CRLF SET @SQL = @SQL + 'WHERE ' + @DateColumnName + ' < ''' + @CutOffDate + '''' + @CRLF SET @SQL = @SQL + 'IF @@ERROR = 0' + @CRLF SET @SQL = @SQL + ' BEGIN' + @CRLF SET @SQL = @SQL + ' COMMIT TRANSACTION' + @CRLF SET @SQL = @SQL + ' END' + @CRLF SET @SQL = @SQL + 'ELSE' + @CRLF SET @SQL = @SQL + ' BEGIN' + @CRLF SET @SQL = @SQL + ' ROLLBACK TRANSACTION' + @CRLF SET @SQL = @SQL + ' END' + @CRLF PRINT @SQL -- And finally, If @PrintOnly = 0, we execute the commands, otherwise the command string -- will just be printed IF @PrintOnly = 0 BEGIN EXEC sp_executesql @SQL END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO