sql-server - ',' 附近的语法不正确。执行 sp_executesql

Question

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

错误是:

Incorrect syntax near ','

我不明白整个查询是否没有零错误。

非常感谢您的帮助

Answer 1

试试这个:

1. 您错过了选择
2. 使用 '''' 代替 \"\"

3. 使用打印输出来调试查询

    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