I'm trying to make a dynamic WHERE clause if the parameter is not -1. it means that it isn't NULL (I added -1 on my web app to make some js validations so the default parameter is -1, which means nothing selected on the filter just in case your wondering why -1 and not NULL is on my validation)

The problems here are 2:

  1. If I run the exec(@sql) sql returns error converting nvarchar value 'insert dynamic query here'. to data type int I don't know what I'm doing wrong?

  2. I don't know how make the condition for the ANDs. I tried to run the query but still adding the ADD condition even when I got -1 on the parameter which means it doesn't need to appear on the query, because it is NULL.

I already tried to add some quotes and stuff on the + part on the query but nothing seems to work

USE db_example
GO
/****** Object:  StoredProcedure [dbo].[proc_FiltroLiq]    Script Date: 29/04/2019 04:03:54 a. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_FiltroLiq] 
@var1 varchar(100),@VAR2 varchar(100),@var3 varchar(100),@var4 varchar(100),@var5 varchar(100),@var6 varchar(100),@var7 varchar(100),
@var8 varchar(100),@var9 varchar(100),@var10 varchar(100),@var11 varchar(100),@var12 varchar(100)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000);
-------DECLARACION DE VARIABLES PARA RECIBIR PARAMETROS, A LA DERECHA SE ENCUENTRA EL NOMBRE DEL CAMPO QUE ESTARA FILLEANDO LA VARIABLE
--declare @var1--PENDIENTE------
--declare @var2 varchar ; --DXZONA
--declare @var3 varchar ; --DXPLAZA
--declare @var4 varchar ; --DXTIENDA
--declare @var5 varchar ; --PENDIENTE------
--declare @var6 varchar ; --DXSUBDIRECCION
--declare @var7 varchar ; --DXCOORDINACION
--declare @var8 varchar ; --DXDEPTO
--declare @var9 varchar ; --DXFAMILIA
--declare @var10 varchar ; --DXPROVEEDOR
--declare @var11 varchar ; --DXMARCA
--declare @var12 varchar ; --DXSUBFAMILIA

Set @SQL= 'select cxupc as "UPC",cncodigointerno as Material,dxupc as Producto,dxsubdireccion as SubDireccion,dxcoordinacion as Coordinacion,dxdepto
as Departamento,dxfamilia as Familia,dxsubfamilia as SubFamilia,dxproveedor As Proveedor,dxmarca as Marca,dxzona as Zona,dxtienda as Sucursal,
mnunidadesinventario as "Inventario Unidades",mminventarioventa as "Inventario Venta",mnporcentajedescuentoactual AS "% Descuento Actual",
cdfechainicialdesc AS "Vigencia Inicial Descuento",mmprecioventaunitario as "Precio de venta",mmpreciorebajado as "Precio Rebajado"
 from PoliticaLiq ';
 --exec(@sql)
IF((@var1 IS NULL OR @var1 = '-1') AND (@VAR2 IS NULL OR @VAR2 = '-1') AND (@VAR3 IS NULL OR @VAR3 = '-1') 
AND (@VAR4 IS NULL OR @var4 = '-1') AND (@VAR5 IS NULL OR @var5 = '-1') AND (@VAR6 IS NULL OR @var6 = '-1')
 AND (@VAR7 IS NULL OR @var7 = '-1') AND (@VAR8 IS NULL OR @var8 = '-1') AND (@VAR9 IS NULL OR @var9 = '-1') 
 AND (@VAR10 IS NULL OR @var10 = '-1') AND (@VAR11 IS NULL OR @var11 = '-1') AND (@VAR12 IS NULL OR @var12 = '-1'))

BEGIN   
    SET NOCOUNT ON;

select TOP 10 cxupc as "UPC",cncodigointerno as Material,dxupc as Producto,dxsubdireccion as SubDireccion,dxcoordinacion as Coordinacion,dxdepto
as Departamento,dxfamilia as Familia,dxsubfamilia as SubFamilia,dxproveedor As Proveedor,dxmarca as Marca,dxzona as Zona,dxtienda as Sucursal,
mnunidadesinventario as "Inventario Unidades",mminventarioventa as "Inventario Venta",mnporcentajedescuentoactual AS "% Descuento Actual",
cdfechainicialdesc AS "Vigencia Inicial Descuento",mmprecioventaunitario as "Precio de venta",mmpreciorebajado as "Precio Rebajado"
 from PoliticaLiq
 group by cxupc,cncodigointerno,dxupc,dxsubdireccion,dxcoordinacion,dxdepto,dxfamilia,dxsubfamilia,dxproveedor,dxmarca,dxzona,dxtienda,
 mnunidadesinventario,mminventarioventa,mnporcentajedescuentoactual,cdfechainicialdesc,mmprecioventaunitario,mmpreciorebajado
END

--PENDIENTE PARA CAMPO DE TABLA
IF((@var1 IS NOT NULL and @VAR1 !='-1'))
    SET @SQL = @SQL + 'WHERE dxsubdireccion''' + @var1+''
else 
--FIN PENDIENTE
IF((@var2 IS NOT NULL  and @VAR2 !='-1') and (@VAR1 = '-1'))
        SET @SQL = @SQL + ' where dxzona = ''' + @var2 + ''''

ELSE
    SET @SQL = @SQL + ' AND  dxzona = ''' + @VAR2+'' 
IF((@var3 IS NOT NULL and @VAR3 != '-1') and (@VAR1 = '-1' )and (@VAR2 = '-1'))
BEGIN
    SET @SQL = @SQL + ' 
                WHERE dxplaza  = ' + @VAR3
END
ELSE
    SET @SQL = @SQL + ' 
                AND dxplaza =' + @VAR3
IF((@var4 IS NOT NULL  and @VAR4 != '-1') and (@VAR1 = '-1') and(@VAR2 = '-1') AND (@VAR3 = '-1'))
BEGIN
    SET @SQL = @SQL + '  
                WHERE dxtienda  =' + @VAR4
END
ELSE
    SET @SQL = @SQL + ' 
                AND dxtienda  =' + @VAR4
--IF((@var5 IS NOT NULL and @VAR5 != '-1') and (@VAR1 = '-1') and (@VAR2 = '-1') AND (@VAR3 = '-1') AND (@VAR4 = '-1'))
--PENDIENTE PARA FILTRO DE ALMACEN
--BEGIN
--  SET @SQL = @SQL + ' 
--                WHERE CAMPO5 = ' + @VAR5 + ' '
--END
--ELSE
--  SET @SQL = @SQL + ' 
 --               AND  = CAMPO5' + @VAR5 + ' '
-- FIN PENDIENTE
IF((@var6 IS NOT NULL and @VAR6 != '-1')  and (@VAR1 = '-1') and (@VAR2 = '-1') AND (@VAR3 = '-1') AND (@VAR4 = '-1') AND (@VAR5 = '-1'))
BEGIN
    SET @SQL = @SQL + ' 
                WHERE dxsubdireccion = ' + @VAR6
END
ELSE
    SET @SQL = @SQL + ' 
                AND dxsubdireccion = ' + @VAR6
IF((@var7 IS NOT NULL and @VAR7 != '-1')  and (@VAR1 = '-1') and (@VAR2 = '-1') AND (@VAR3 = '-1') AND (@VAR4 = '-1') AND (@VAR5 = '-1') AND (@VAR6 = '-1'))
BEGIN
    SET @SQL = @SQL + ' 
                WHERE dxcoordinacion = ' + @VAR7
END
ELSE
    SET @SQL = @SQL + ' 
                AND dxcoordinacion = ' + @VAR7
IF((@var8 IS NOT NULL and @VAR8 != '-1') and (@VAR1 = '-1') and (@VAR2 = '-1') AND (@VAR3 = '-1') AND (@VAR4 = '-1') AND (@VAR5 = '-1') AND (@VAR6 = '-1') AND (@VAR7 = '-1'))
BEGIN
    SET @SQL = @SQL + ' 
                WHERE  dxdepto = ' + @VAR8
END
ELSE
    SET @SQL = @SQL + ' 
                AND dxdepto = ' + @VAR8
IF((@var9 IS NOT NULL and @VAR9 != '-1') and (@VAR1 = '-1') and (@VAR2 = '-1') AND (@VAR3 = '-1') AND (@VAR4 = '-1') AND (@VAR5 = '-1') AND (@VAR6 = '-1') AND (@VAR7 = '-1') AND (@VAR8 = '-1'))
BEGIN
    SET @SQL = @SQL + ' 
                WHERE dxfamilia = ' + @VAR9
END
ELSE
    SET @SQL = @SQL + ' 
                AND dxfamilia = ' + @VAR9
IF((@var10 is not null and @VAR10 != '-1') and (@VAR1 = '-1') and (@VAR2 = '-1') AND (@VAR3 = '-1') AND (@VAR4 = '-1') AND (@VAR5 = '-1') AND (@VAR6 = '-1') AND (@VAR7 = '-1') AND (@VAR8 = '-1') AND (@VAR9 = '-1'))
BEGIN
    SET @SQL = @SQL + ' 
                WHERE dxproveedor = ' + @VAR10
END
ELSE
    SET @SQL = @SQL + ' 
                AND dxproveedor = ' + @VAR10
IF((@var11 IS NOT NULL  and @VAR11 != '-1') and (@VAR1 = '-1') and (@VAR2 = '-1') AND (@VAR3 = '-1') AND (@VAR4 = '-1') AND (@VAR5 = '-1') AND (@VAR6 = '-1') AND (@VAR7 = '-1') AND (@VAR8 = '-1') AND (@VAR9 = '-1') AND (@VAR10 = '-1'))
BEGIN
    SET @SQL = @SQL + ' 
                WHERE dxmarca = ' + @VAR11
END
ELSE
    SET @SQL = @SQL + ' 
                AND dxmarca = ' + @var11
IF((@var12 IS NOT NULL and @VAR12 != '-1') and (@VAR1 = '-1') and (@VAR2 = '-1') AND (@VAR3 = '-1') AND (@VAR4 = '-1') AND (@VAR5 = '-1') AND (@VAR6 = '-1') AND (@VAR7 = '-1') AND (@VAR8 = '-1') AND (@VAR9 = '-1') AND (@VAR10 = '-1') AND (@VAR11 = '-1'))
BEGIN
    SET @SQL = @SQL + ' 
                WHERE dxsubfamilia = ' + @VAR12
END
ELSE
begin
    SET @SQL = @SQL + ' 
                AND dxsubfamilia = ' + @VAR12       
                end
return @sql
EXEC(@SQL)
end

I expect to make a dynamic query but I'm very confused about what to do to make it work.

0 Answers