如何将输入参数传递给sp_executesql？ [英] How do I pass input parameters to sp_executesql?

问题描述

在SQL Server 2014中，我尝试创建动态WHERE子句。

In SQL Server 2014, I am trying to create a dynamic WHERE clause.

我已将查询构建为字符串，但是当我尝试使用字符串执行时sp_executesql，出现以下错误：
行号13您必须声明标量变量 @desde。

I have built the query as a string, but when I try to execute it with sp_executesql, I get the following error: Líne 13 You must declare the scalar variable "@desde".

我可以t弄清楚如何使sp_executesql识别输入参数。

I can't figure out how to get sp_executesql to recognize the input parameters.

ALTER PROCEDURE [dbo].[seleccionarFacturas] 
    -- Add the parameters for the stored procedure here
    @desde char(8) = null, 
    @hasta char(8) = null,
    @minimo int = null,
    @ciudad int = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @tiendas varchar(max);

    DECLARE @tablaFacturas TABLE
    (
        fecha char(8), 
        CO char(8),
        consecutivo varchar(max),
        nombreCliente varchar(max),
        ventaTotal int
    );

    SET @tiendas='(ID_CO=20 OR ID_CO=22 OR ID_CO=23 OR ID_CO=27 OR ID_CO=35 OR ID_CO=39 OR ID_CO=45 OR ID_CO=48 OR ID_CO=55 OR ID_CO=58)';

    DECLARE @dynamicCode nvarchar(max)=
    N'
    SELECT
        FECHA_DCTO,
        ID_CO,
        DETALLE_DOC,
        NOM_CLI_CONTADO,
        (SUM(TOT_VENTA)) AS ventaTotal
    FROM 
        moda.dbo.CMMOVIMIENTO_VENTAS
    WHERE'
        + @tiendas +
        N' AND FECHA_DCTO >= @desde
        AND FECHA_DCTO <= @hasta
    GROUP BY  
        DETALLE_DOC, ID_CO, FECHA_DCTO, NOM_CLI_CONTADO';

    INSERT INTO @tablaFacturas
    EXEC [dbo].[sp_executesql] @dynamicCode;

    SELECT * FROM @tablaFacturas

推荐答案

而不是

EXEC [dbo].[sp_executesql] @dynamicCode;

使用

EXECUTE sp_executesql @dynamicCode, 
N'@desde char(8), @hasta char(8)',
@desde = @desde, @hasta = @hasta;

您必须定义在动态查询中使用的参数，例如 @desde和@hasta

You have to define the parameters you used in the dynamic query like@desde and @hasta

请参考 sp_executesql

其他您可以合并 @desde，@hasta 到动态查询，
就像

Else You can concat the values of @desde, @hasta to the dynamic query, like

'....FECHA_DCTO >= ' + @desde +
'AND FECHA_DCTO <= ' + @hasta +
'GROUP BY ....'

这篇关于如何将输入参数传递给sp_executesql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！