Parameterized dynamic query within cursor causes ERROR "Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'"

I need to get the max edit date for each table in our database and store in a temp table. The cursor works fine but when I run exec sp_executesql @sql I get a parameter expectation error:

Parameterized dynamic query within Cursor gives ERROR Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'

What am I doing wrong?

SET NOCOUNT ON

IF OBJECT_ID('tempdb..##GetMaxVistaEditDate') IS NOT NULL 
    DROP TABLE ##GetMaxVistaEditDate

CREATE TABLE ##GetMaxVistaEditDate 
(
     MySchema nvarchar(max), 
     MyTable nvarchar(max),  
     MaxVistaEditDate DateTime
)

-- SELECT * FROM ##GetMaxVistaEditDate

DECLARE MyCursor CURSOR FOR
    SELECT 
        SCHEMA_NAME(t.schema_id) Schemaname,
        t.name AS TableName
    FROM 
        sys.tables t 
    WHERE  
        Schema_Name(t.Schema_id) like 'R_PERS%'

OPEN MyCursor

DECLARE @Schema VARCHAR(100), @Table VARCHAR(100), @MaxVistaEditDate DATETIME
DECLARE @sql NVARCHAR(MAX) = '', @params NVARCHAR(MAX);

SET @params = N'@MaxVistaEditDate DateTime OUTPUT';

FETCH FROM MyCursor INTO @Schema, @Table

WHILE @@FETCH_STATUS = 0
BEGIN   
    SET @SQL = 'DECLARE @MaxVistaEditDate DATETIME SELECT @MaxVistaEditDate =  (SELECT MAX(VistaEditDate) FROM ' + @SCHEMA + '.' + @TABLE   + ')'   

    EXEC sp_executesql @sql, @MaxVistaEditDate OUTPUT
    -- PRINT @SQL
    -- PRINT @MaxVistaEditDate

    INSERT INTO ##GetMaxVistaEditDate 
        SELECT @Schema, @Table, @MaxVistaEditDate

    FETCH FROM MyCursor INTO @Schema, @Table
END

CLOSE MyCursor
DEALLOCATE MyCursor

Solution

You don't have to declare the variables on the sql string, you have to do it on a different variable, and you already have one for that (you name it @params).

Change your @sql definition for the following

SET @SQL = 'Select @MaxVistaEditDate =  (SELECT MAX(VistaEditDate)   From  ' + @SCHEMA + '.' + @TABLE    + ')'

And change your call for this:

exec sp_executesql @sql ,@params, @MaxVistaEditDate = @MaxVistaEditDate OUTPUT

and it should work.

Note: Don't forget to close and deallocate the cursor.