不正确的语法”、“附近。exec sp_executesql

ALTER PROCEDURE [dbo].[proc_bandejaPedidos]
    @id_usuario INT = 0,
    @selector NVARCHAR(50) = '',
    @order NVARCHAR(50) = '',
    @consulta NVARCHAR(MAX) = ''
AS
BEGIN
    SET NOCOUNT ON;

    IF(@selector = '' AND @order = '')
            SET @selector = 'pedi.fecha_creacion';
            SET @order = 'DESC';

    SET @consulta = 'pedi.id,pedi.folio,pedi.cliente_id,
                     eniac_clientes.rfc,
                     eniac_clientes.nombre+\" \"+eniac_clientes.apellido_paterno+\" \"+eniac_clientes.apellido_paterno AS Cliente,
                     pedi.fecha_creacion,
                     pedi.importe,
                     estatus_pi.estatus,
                     eniac_sucursales.nombre AS sucursal

                     FROM pedidos AS pedi

    INNER JOIN gestion_pedidos_eniac.dbo.sucursales AS eniac_sucursales ON eniac_sucursales.id_sucursal = pedi.sucursal_id
    INNER JOIN gestion_pedidos_eniac.dbo.clientes AS eniac_clientes ON eniac_clientes.id = pedi.cliente_id
    INNER JOIN estatus_pedido AS estatus_pi ON estatus_pi.id = pedi.estatus

    WHERE pedi.usuario_id = '+ CAST(@id_usuario AS NVARCHAR)+'
    ORDER BY ' + @selector + ' '+ @order + ''

    EXEC sp_executesql @consulta

END

Error is:

错误:

Incorrect syntax near ','

附近的错误语法”、“

and I do not understand that if the entire query is fine without zero errors.

而且我不明白如果整个查询没有零错误就很好。

Thank you very much for your help

非常感谢你的帮助

4 个解决方案

#1

try this:

试试这个:

You have missed select
你错过了选择
Use '''' instead of \" \"
用“”代替“\”

Debug Query Using Printing its output

使用打印输出的调试查询。

 ALTER PROCEDURE [dbo].[proc_bandejaPedidos]

    @id_usuario INT = 0,
    @selector NVARCHAR(50) = '',
    @order NVARCHAR(50) = '',
    @consulta NVARCHAR(MAX) = ''
AS

BEGIN

    SET NOCOUNT ON;
    IF(@selector = '' AND @order = '')
            SET @selector = 'pedi.fecha_creacion';
            SET @order = 'DESC';

    SET @consulta = 'select pedi.id,pedi.folio,pedi.cliente_id,
                     eniac_clientes.rfc,
                     eniac_clientes.nombre+'' ''+eniac_clientes.apellido_paterno+'' ''+eniac_clientes.apellido_paterno AS Cliente,
                     pedi.fecha_creacion,
                     pedi.importe,
                     estatus_pi.estatus,
                     eniac_sucursales.nombre AS sucursal

                     FROM pedidos AS pedi

    INNER JOIN gestion_pedidos_eniac.dbo.sucursales AS eniac_sucursales ON eniac_sucursales.id_sucursal = pedi.sucursal_id
    INNER JOIN gestion_pedidos_eniac.dbo.clientes AS eniac_clientes ON eniac_clientes.id = pedi.cliente_id
    INNER JOIN estatus_pedido AS estatus_pi ON estatus_pi.id = pedi.estatus

    WHERE pedi.usuario_id = '+ CAST(@id_usuario AS NVARCHAR)+'
    ORDER BY ' + @selector + ' '+ @order + ''

    EXEC sp_executesql @consulta


  print (@consulta) END

#2

SET @consulta = 'pedi.id,pedi.folio,pedi.cliente_id,
should be
SET @consulta = 'SELECT pedi.id,pedi.folio,pedi.cliente_id,

设置@consulta = ' pedi.id pedi.folio,没效果。应该将cliente_id设置为@consulta = 'SELECT pedi.id,pedi.folio,pedi.cliente_id，

#3

You have missed select in your statement.

您在声明中漏掉了select。

It should be SET @consulta = 'select pedi.id,pedi.folio,pedi.cliente_id, and so on

它应该设置为@consulta = 'select pedi.id,pedi.folio,pedi。cliente_id等等

#4

You have some issues in your code.

您的代码中有一些问题。

Add SELECT to the start of your code: 'SELECT pedi.id,pedi.folio, ...
在代码的开头添加SELECT: 'SELECT .id,pedi。对开本的书,……
Add BEGIN/END to the IF clause, as right now the second line runs always:
IF(@selector = '' AND @order = '') SET @selector = 'pedi.fecha_creacion'; SET @order = 'DESC';
将BEGIN/END添加到IF子句，此时第二行始终运行:IF(@selector = " AND @order = ")设置@selector = 'pedi.fecha_creacion';设置@order =“DESC”;
You doesn't filter your input and the SQL Injection can occur
不过滤输入，就会发生SQL注入
Use '' instead of \"
使用“而不是\”

#1