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

Question

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

MySQL or SQL Server? They are totally different products with quite different SQL variants. — Dale K, Jun 23 '20 at 02:16
Assuming SQL Server I recommend reading the [official docs](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) and comparing their examples with yours. — Dale K, Jun 23 '20 at 02:22

score 0 · Answer 1 · answered Jun 23 '20 at 03:21

you can find an answer in this post SP_EXECUTESQL and Output Parameter and your sp_executesql statement don't have parameter definition and you don't have to declare a variable inside the dynamic query

declare @MaxVistaEditDate datetime
exec sp_executesql @sql ,N'@MaxVistaEditDateOut datetime OutPut,  @MaxVistaEditDateOut=@MaxVistaEditDate OUTPUT

score 0 · Accepted Answer · answered Jun 23 '20 at 03:32

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.

I tried the other answer before yours and got more errors. Your answer precisely resolved my issue. Thank you so much! — user3525187, Jun 23 '20 at 12:46

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

2 Answers2