Execute the result of a dynamic SQL using SP_EXECUTESQL

Recently, I came across T-SQL procedure which is developed to take backups of MS SQL tables in a separate database (i.e. Trash ) with 'xxx_TableName_YYYYMMDD' format. Since the proc keeps on adding backup tables every time it gets executed, the following query will limit it to last 5 tables in the trash database.



The following code also shows how to execute a result of a dynamic query (@SQL1) in another dynamic query (@SQL3) using SP_EXECUTESQL system procedure.



--------------------------------------
-- Keep the last 5 backup tables and delete the rest for a given table in Trash DB
DECLARE @Source_Table VARCHAR(100)
DECLARE @Backup_Table VARCHAR(100)
DECLARE @Backup_Table_Prefix VARCHAR(100)
DECLARE @WhereClause VARCHAR(Max) 
DECLARE @Date VARCHAR(8)
DECLARE @SQL1 NVARCHAR(MAX) ;
DECLARE @SQL3 NVARCHAR(MAX) = '' ;
--------------------------------------
BEGIN
 SET @Source_Table = 'Test_Table'
 SET @Backup_Table_Prefix = 'xxx_'
 SET @Date = CONVERT(VARCHAR(8),GETDATE(),112) -- YYYYMMDD
 SET @Backup_Table = @Backup_Table_Prefix + @Source_Table + '_' + @Date -- 'xxx_Test_table_YYYYMMDD'
 SET @WhereClause =  @Backup_Table_Prefix + @Source_Table + '_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
 --PRINT @WhereClause
 
 SET @SQL1 =  
  'SELECT @SQL2 += '' DROP TABLE trash.dbo.'''  + ' + a.name + '' ; ''' +
  ' FROM trash.sys.tables a
   JOIN (
    SELECT name,
    RankRows = ROW_NUMBER()over (ORDER by name DESC) 
    FROM trash.sys.tables 
    WHERE name like ''' + @WhereClause 
   + ''' ) b ON a.name = b.name WHERE b.RankRows > 5; '

 IF @SQL1 IS NOT NULL AND  @SQL1 <> ''
  --PRINT @SQL1; 
  EXEC SP_EXECUTESQL @SQL1 , N'@SQL2 NVARCHAR(MAX) OUTPUT', @SQL3 OUTPUT

 IF @SQL3 IS NOT NULL AND  @SQL3 <> ''
  --PRINT @SQL3; 
  EXEC SP_EXECUTESQL @SQL3
END
--------------------------------------