How do I pass input parameters to sp_executesql?
14,054
Instead of
EXEC [dbo].[sp_executesql] @dynamicCode;
Use
EXECUTE sp_executesql @dynamicCode,
N'@desde char(8), @hasta char(8)',
@desde = @desde, @hasta = @hasta;
You have to define the parameters you used in the dynamic query like@desde and @hasta
Please refer sp_executesql
Else You can concat the values of @desde, @hasta
to the dynamic query,
like
'....FECHA_DCTO >= ' + @desde +
'AND FECHA_DCTO <= ' + @hasta +
'GROUP BY ....'
Comments
-
quelquecosa almost 2 years
In SQL Server 2014, I am trying to create a dynamic WHERE clause.
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".
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
-
Jack over 7 yearsthe concatenation one might be injected. I'd highly suggest to use the first option, where dynamic code is something like => 'field=@par_field ...'