DECLARE
@SQLsource VARCHAR(1000),
@ArchiveBatchID INT,
@ArchiveControlField VARCHAR(20),
@TableName VARCHAR(20),
@ArchiveSourceDateField VARCHAR(20),
@ArchiveCutOffDate DATETIME
SELECT
@ArchiveBatchID = 97,
@ArchiveControlField = 'ControlColumn',
@TableName = 'MyTable',
@ArchiveSourceDateField = 'SourceDate',
@ArchiveCutOffDate = GETDATE()
SET @SQLsource = 'SELECT '
+ CAST(@ArchiveBatchID AS varchar) + ', '
+ @ArchiveControlField
+ ' FROM ' + 'AAD..' + @TableName
+ ' WHERE ' + @ArchiveSourceDateField
+ ' <= '''
+ CONVERT(VARCHAR(12),@ArchiveCutOffDate,112) + ''''
SELECT @SQLsource
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden