存储过程巧用 参数实现 条件筛选

1. 介绍： 有的时候一个存储过程里面既要实现无条件筛选，也要实现条件筛选的组合,可以采用以下的便捷方式

2. 对于字符串的条件筛选：

       IF ISNULL(@name, '') = ''
       BEGIN
           SET @name = '';
       END
       ELSE
       BEGIN
           SET @name = '%' + @name + '%';
       END
   
       IF ISNULL(@supplierName, '') = ''
       BEGIN
           SET @supplierName = '';
       END
       ELSE
       BEGIN
           SET @supplierName = '%' + @supplierName + '%';--拼接好直接使用
       END
       
       
       
       SELECT esi.Id
           ,esi.NAME
           ,esi.Usage
           ,esi.Standard
       FROM ESD_SampleItem esi
       WHERE esi.CompanyCode = @companyCode
           AND esi.IsDeleted = 0
           AND esi.IsActiveVersion = 1
           AND (
               @name = '' --如果为 null 筛选全部，用 OR 来分割操作
               OR esi.NAME LIKE @name --如果不为null,模糊查询
               )
               --或者如下
            AND (
               ISNULL(@system, '') = ''
               OR d.Systems LIKE '%,' + @system + ',%'
               )
           AND (
               @supplierName = ''
               OR EXISTS (
                   SELECT 1
                   FROM ESD_SampleItemSupplier esis
                   WHERE esis.SampleItemId = esi.Id
                       AND esis.SupplierName LIKE @supplierName
                   )
               )
       ORDER BY esi.Id offset(@pageIndex - 1) * @pageSize rows
   
       FETCH NEXT @pageSize rows ONLY

3. 对于值类型的条件筛选

   AND (
                   @supplier <= 0 -- @supplier <= 0 查询全部
                   OR s.SupplierId = @supplier --查询与之对应的条件
                   )