How can I use sp_executesql to execute a query mounted on a variable?

Question

I’m learning to use cursors and I’m trying to run a query I’ve mounted within a variable but it’s giving the following error:

"Message 214, Level 16, Status 2, Procedure sp_executesql, Line 1 Procedure expects Parameter '@statement' of type 'ntext/nchar/nvarchar'."

I’m not getting it. It follows below the code I built.

'''

    SET NOCOUNT ON;

    DECLARE @COLUNA VARCHAR(100), @COLUNAS VARCHAR(MAX), @CLONAR VARCHAR(MAX), @NovoId int, @Id 
    varchar(11);

    --- Inicializar a variavel
    SET @COLUNAS    = ''
    SET @NovoId     = ''
    SET @CLONAR     = ''
    SET @Id = '1234'

    --sempre declare que o cursor é do tipo LOCAL FAST_FORWARD.
    --Local diz que é o scopo da execuçao e FAST_FORWARD significa que ele so vai pra frente.
    DECLARE CURCOLUNAS CURSOR LOCAL FAST_FORWARD FOR SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_CATALOG   = 'MeuBanco'
            AND TABLE_SCHEMA= 'dbo'
            AND TABLE_NAME  = 'MinhaTabela'
    ORDER BY ORDINAL_POSITION
    OPEN CURCOLUNAS
        FETCH NEXT FROM CURCOLUNAS INTO @COLUNA
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @COLUNAS = @COLUNAS + ',' + @COLUNA
            FETCH NEXT FROM CURCOLUNAS INTO @COLUNA --- O into nesta linha
        END

        SET @COLUNAS= Right(@COLUNAS, len(@COLUNAS)-1);
        SET @CLONAR = N'SELECT '+@COLUNAS+' FROM MinhaTabela WHERE Id = '+@Id;
        print @CLONAR;

        SET IDENTITY_INSERT [MeuBanco].[dbo].[MinhaTabela] ON
        EXECUTE sp_executesql @CLONAR;
        SET IDENTITY_INSERT [MeuBanco].[dbo].[MinhaTabela] OFF

        --SET @NovoId=SCOPE_IDENTITY()
        --PRINT  @NovoId
    CLOSE CURCOLUNAS;
    DEALLOCATE CURCOLUNAS;

'''

Answer 1

Hi, you only need to change the @CLONE variable to NVARCHAR, is what the error is indicating..."...expects Parameter '@statement' of type 'ntext/nchar/nvarchar'."

   SET NOCOUNT ON;

    DECLARE @COLUNA VARCHAR(100), @COLUNAS VARCHAR(MAX), @CLONAR NVARCHAR(MAX), @NovoId int, @Id 
    varchar(11);

    --- Inicializar a variavel
    SET @COLUNAS    = ''
    SET @NovoId     = ''
    SET @CLONAR     = ''
    SET @Id = '1234'

    --sempre declare que o cursor é do tipo LOCAL FAST_FORWARD.
    --Local diz que é o scopo da execuçao e FAST_FORWARD significa que ele so vai pra frente.
    DECLARE CURCOLUNAS CURSOR LOCAL FAST_FORWARD FOR SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_CATALOG   = 'MeuBanco'
            AND TABLE_SCHEMA= 'dbo'
            AND TABLE_NAME  = 'MinhaTabela'
    ORDER BY ORDINAL_POSITION
    OPEN CURCOLUNAS
        FETCH NEXT FROM CURCOLUNAS INTO @COLUNA
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @COLUNAS = @COLUNAS + ',' + @COLUNA
            FETCH NEXT FROM CURCOLUNAS INTO @COLUNA --- O into nesta linha
        END

        SET @COLUNAS= Right(@COLUNAS, len(@COLUNAS)-1);
        SET @CLONAR = N'SELECT '+@COLUNAS+' FROM MinhaTabela WHERE Id = '+@Id;
        print @CLONAR;

        SET IDENTITY_INSERT [MeuBanco].[dbo].[MinhaTabela] ON
        EXECUTE sp_executesql @CLONAR;
        SET IDENTITY_INSERT [MeuBanco].[dbo].[MinhaTabela] OFF

        --SET @NovoId=SCOPE_IDENTITY()
        --PRINT  @NovoId
    CLOSE CURCOLUNAS;
    DEALLOCATE CURCOLUNAS;