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.

`'[' +@nombreCampoTabla +']` is **not** injection safe! Just like when you inject a statement when using a literal string for a "parameter" (you can escape the value by using `'`), you can do the same with an object name (using a `]`). If you *need* to use dynamic objects, then use `QUOTENAME`; as it'll escape any characters (so a value like `'test]; DROP TABLE [test]'` would become `''test]] DROP TABLE [test]]]'`. — Thom A, Sep 28 '19 at 18:34
The error, however, is telling you the error; `@Params` is expected to be an `nvarchar`, and you have a `bigint`. [sp_executesql (Transact-SQL): Examples - A. Executing a simple SELECT statement](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql#a-executing-a-simple-select-statement) — Thom A, Sep 28 '19 at 18:35

Dan Guzman · Answer 1 · 2019-09-28T22:19:19.910

3

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;

edited Sep 28 '19 at 22:19

answered Sep 28 '19 at 18:21

Dan Guzman

43,250
3
46
71

I think there is a small error in `QUAOTENAME(@nombreCampoTabla)`, +1 from my side. – Zhorov Sep 28 '19 at 20:34
@Zhorov, I fixed the typo in my example. There are other errors in the query but I think this example should get you going. – Dan Guzman Sep 28 '19 at 22:20

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

1 Answers1