Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar' SQL Server

Question

I have a problem with code in SQL Server. The error is:

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

My code:

CREATE FUNCTION porcentajeCadaUno  -- Se ingresa las variables del procedimiento almacenado para calcular % --
    (@cantidad INT,
     @idEvento BIGINT)
RETURNS FLOAT
AS
BEGIN
    RETURN CAST((@cantidad) * 100 AS FLOAT) / 
           (SELECT (dbo.cuentaPersonasConEvento_Leidos(@idEvento))) 
END

CREATE PROCEDURE porcentajeCadaCampoTabla
    @nombreCampoTabla NVARCHAR(100),
    @idEvento BIGINT
AS
BEGIN
    DECLARE @stmt NVARCHAR(MAX), @params BIGINT

    SET @params = @idEvento

    SET @stmt = 'select [' +@nombreCampoTabla +'],count(['+@nombreCampoTabla+']), 
dbo.porcentajeCadaUno (count(['+@nombreCampoTabla+']),@params) from Invitado as inv inner join 
(select idInvitado from Invitado intersect select idInvitado_FK from Leido) as le on  le.idInvitado=inv.idInvitado
where inv.idEvento_FK=@params group by ['+@nombreCampoTabla+']'

    EXEC sp_executesql @stmt, @params
END

EXEC porcentajeCadaCampoTabla 'generoInvitado', 1

I expect that the output show me a table with 3 columns, the first, name of variables, the second (how many), and the third the percent.

Answer 1

sp_executesql needs 3 parameters here: SQL statement, parameter declaration, and the parameter value. Below is an example, where I've added the QUOTENAME function to ensure identifiers are properly enclosed and escaped:

SET @stmt = 'select ' + QUOTENAME(@nombreCampoTabla) +',count('+QUOTENAME(@nombreCampoTabla)+'), 
dbo.porcentajeCadaUno (count(['+@nombreCampoTabla+']),@params) from Invitado as inv inner join 
(select idInvitado from Invitado intersect select idInvitado_FK from Leido) as le on  le.idInvitado=inv.idInvitado
where inv.idEvento_FK=@params group by '+QUOTENAME(@nombreCampoTabla)+';';

EXEC sp_executesql  @stmt, N'@params bigint', @params = @idEvento;