SQL Server 2008动态WHERE子句转换错误及逻辑问题求助

看起来你在写动态筛选的存储过程时踩了两个常见的坑：字符串拼接导致的类型转换错误，以及错误的条件逻辑让-1参数仍被加入筛选。下面是修复后的完整存储过程，同时优化了动态SQL的安全性和可读性：

USE db_example 
GO 
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 
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX); -- 扩大容量避免查询语句被截断
    DECLARE @Params NVARCHAR(MAX); -- 参数化查询的参数定义语句

    -- 初始化基础查询，用WHERE 1=1简化后续条件拼接逻辑
    SET @SQL = N'
        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 
        WHERE 1=1 '

    -- 定义参数映射关系，用于参数化查询
    SET @Params = N'
        @p1 varchar(100), @p2 varchar(100), @p3 varchar(100), @p4 varchar(100),
        @p5 varchar(100), @p6 varchar(100), @p7 varchar(100), @p8 varchar(100),
        @p9 varchar(100), @p10 varchar(100), @p11 varchar(100), @p12 varchar(100)'

    -- 逐个处理参数：仅当参数不为-1且非空时，添加对应的筛选条件
    IF @var1 IS NOT NULL AND @var1 != '-1'
        SET @SQL = @SQL + N' AND dxsubdireccion = @p1 ' -- 注意：@var1对应的字段你标注为PENDIENTE，需根据实际表结构调整

    IF @VAR2 IS NOT NULL AND @VAR2 != '-1'
        SET @SQL = @SQL + N' AND dxzona = @p2 '

    IF @var3 IS NOT NULL AND @var3 != '-1'
        SET @SQL = @SQL + N' AND dxplaza = @p3 '

    IF @var4 IS NOT NULL AND @var4 != '-1'
        SET @SQL = @SQL + N' AND dxtienda = @p4 '

    -- @var5对应的字段未标注，需补充时取消下面的注释并修改字段名
    -- IF @var5 IS NOT NULL AND @var5 != '-1'
    --     SET @SQL = @SQL + N' AND [你的字段名] = @p5 '

    IF @var6 IS NOT NULL AND @var6 != '-1'
        SET @SQL = @SQL + N' AND dxsubdireccion = @p6 '

    IF @var7 IS NOT NULL AND @var7 != '-1'
        SET @SQL = @SQL + N' AND dxcoordinacion = @p7 '

    IF @var8 IS NOT NULL AND @var8 != '-1'
        SET @SQL = @SQL + N' AND dxdepto = @p8 '

    IF @var9 IS NOT NULL AND @var9 != '-1'
        SET @SQL = @SQL + N' AND dxfamilia = @p9 '

    IF @var10 IS NOT NULL AND @var10 != '-1'
        SET @SQL = @SQL + N' AND dxproveedor = @p10 '

    IF @var11 IS NOT NULL AND @var11 != '-1'
        SET @SQL = @SQL + N' AND dxmarca = @p11 '

    IF @var12 IS NOT NULL AND @var12 != '-1'
        SET @SQL = @SQL + N' AND dxsubfamilia = @p12 '

    -- 执行参数化查询，避免SQL注入和类型转换错误
    EXEC sp_executesql @SQL, @Params,
        @p1 = @var1, @p2 = @VAR2, @p3 = @var3, @p4 = @var4,
        @p5 = @var5, @p6 = @var6, @p7 = @var7, @p8 = @var8,
        @p9 = @var9, @p10 = @var10, @p11 = @var11, @p12 = @var12
END

核心修复和优化说明： #

• 解决类型转换错误：放弃直接字符串拼接，改用sp_executesql执行参数化查询，SQL Server会自动处理类型匹配，彻底避免手动拼接引号时的缺失/冗余问题，同时还能防止SQL注入。
• 修复无效筛选问题：每个参数仅在不为-1且非空时才添加对应的AND条件，完全跳过-1参数的筛选逻辑，不再强制加入无效条件。
• 简化拼接逻辑：用WHERE 1=1作为基础条件，后续所有筛选条件都统一用AND开头，无需判断是否是第一个条件，大幅简化分支逻辑。
• 避免语句截断：将@SQL的类型从NVARCHAR(1000)改为NVARCHAR(MAX)，防止查询语句过长被截断。

内容的提问来源于stack exchange，提问作者Daniel M