sql-server - 如何制作一个按某些条件过滤所有记录的存储过程

Question

大家好，我在制作存储过程时遇到问题，我想制作一个类似的存储过程，其中有 44 种类型的输入参数，其中 20 用于 size1 到 size20，20 用于颜色 Color1-color20，2 用于价格两个百分比，我想根据我的存储过程代码正在闲置的参数过滤记录

USE [MakaAnOrderDB]
GO
/****** Object:  StoredProcedure [dbo].[GetProductByCustomization]    Script Date: 10/8/2018 6:07:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    ALTER PROCEDURE [dbo].[GetProductByCustomization]
        @Size1 NVARCHAR(10) ,
        @Size2  NVARCHAR(10),
        @Size3 NVARCHAR(10) ,
        @Size4  NVARCHAR(10) ,
        @Size5  NVARCHAR(10) ,
        @Size6  NVARCHAR(10) ,
        @Size7  NVARCHAR(10),
        @Size8  NVARCHAR(10) ,
        @Size9  NVARCHAR(10) ,
        @Size10   NVARCHAR(10) ,
        @Size11  NVARCHAR(10) ,
        @Size12  NVARCHAR(10) ,
        @Size13  NVARCHAR(10) ,
        @Size14  NVARCHAR(10) ,
        @Size15  NVARCHAR(10) ,
        @Size16  NVARCHAR(10) ,
        @Size17  NVARCHAR(10),
        @Size18  NVARCHAR(10),
        @Size19  NVARCHAR(10) ,
        @Size20  NVARCHAR(10) ,
        @CategoryId NVARCHAR(255) ,
        @Price1 DECIMAL(18, 0) ,
        @Price2 DECIMAL(18, 0) ,
        @Color1  NVARCHAR(10) ,
        @Color2  NVARCHAR(10) ,
        @Color3  NVARCHAR(10) ,
        @Color4  NVARCHAR(10) ,
        @Color5  NVARCHAR(10) ,
        @Color6  NVARCHAR(10) ,
        @Color7  NVARCHAR(10) ,
        @Color8  NVARCHAR(10) ,
        @Color9  NVARCHAR(10) ,
        @Color10  NVARCHAR(10) ,
        @Color11  NVARCHAR(10) ,
        @Color12  NVARCHAR(10) ,
        @Color13  NVARCHAR(10) ,
        @Color14  NVARCHAR(10) ,
        @Color15  NVARCHAR(10) ,
        @Color16  NVARCHAR(10) ,
        @Color17 NVARCHAR(10) ,
        @Color18  NVARCHAR(10) ,
        @Color19 NVARCHAR(10) ,
        @Color20  NVARCHAR(10) ,
        @DiscountPercentage1 TINYINT ,
        @DiscountPercentage2 TINYINT
    AS
        BEGIN


        SELECT  *
        FROM    tblProduct
        WHERE   ( PrdPrice BETWEEN @Price1 AND @Price2 )
                AND 
                ( PrdOffPercentage BETWEEN @DiscountPercentage1 AND @DiscountPercentage2 )

                AND PrdColor IN ( @Color1, @Color2, @Color3, @Color4, @Color5,
                                  @Color6, @Color7, @Color8, @Color9, @Color10,
                                  @Color11, @Color12, @Color13, @Color14,
                                  @Color15, @Color16, @Color17, @Color18,
                                  @Color19, @Color20 )
                AND PrdSize IN ( @Size1, @Size2, @Size3, @Size4, @Size5,
                                 @Size6, @Size7, @Size8, @Size9, @Size10,
                                 @Size11, @Size12, @Size13, @Size14, @Size15,
                                 @Size16, @Size17, @Size18, @Size19, @Size20 )
                AND PrdCategoryId = @CategoryId

end

我想要一个存储过程，如果我为一个参数输入一个值或为所有参数输入所有值，如果表中匹配一个或多个参数，它会给我结果

标签： sql-servertsqlstored-procedures

Answer 1

就像其他答案所说，研究使用数组作为参数。如果你想这样做，那么为你的参数设置一个默认值，要么是空值，要么是你确定不会命中的东西，或者整个频谱（例如，0-100 的百分比）

  ALTER PROCEDURE [dbo].[GetProductByCustomization]
    @Size1  NVARCHAR(10) = 'NoSize',
    @Size2  NVARCHAR(10) = 'NoSize',
    @Size3  NVARCHAR(10) = 'NoSize',
    @Size4  NVARCHAR(10) = 'NoSize',
    @Size5  NVARCHAR(10) = 'NoSize',
    @Size6  NVARCHAR(10) = 'NoSize',
    @Size7  NVARCHAR(10) = 'NoSize',
    @Size8  NVARCHAR(10) = 'NoSize',
    @Size9  NVARCHAR(10) = 'NoSize',
    @Size10 NVARCHAR(10) = 'NoSize',
    @Size11 NVARCHAR(10) = 'NoSize',
    @Size12 NVARCHAR(10) = 'NoSize',
    @Size13 NVARCHAR(10) = 'NoSize',
    @Size14 NVARCHAR(10) = 'NoSize',
    @Size15 NVARCHAR(10) = 'NoSize',
    @Size16 NVARCHAR(10) = 'NoSize',
    @Size17 NVARCHAR(10) = 'NoSize',
    @Size18 NVARCHAR(10) = 'NoSize',
    @Size19 NVARCHAR(10) = 'NoSize',
    @Size20 NVARCHAR(10) = 'NoSize',
    @CategoryId NVARCHAR(255) =Null,
    @Price1  DECIMAL(18, 0)=0,
    @Price2  DECIMAL(18, 0)=99999999999,
    @Color1  NVARCHAR(10)='NoColor',
    @Color2  NVARCHAR(10)='NoColor',
    @Color3  NVARCHAR(10)='NoColor',
    @Color4  NVARCHAR(10)='NoColor',
    @Color5  NVARCHAR(10)='NoColor',
    @Color6  NVARCHAR(10)='NoColor',
    @Color7  NVARCHAR(10)='NoColor',
    @Color8  NVARCHAR(10)='NoColor',
    @Color9  NVARCHAR(10)='NoColor',
    @Color10 NVARCHAR(10)='NoColor',
    @Color11 NVARCHAR(10)='NoColor',
    @Color12 NVARCHAR(10)='NoColor',
    @Color13 NVARCHAR(10)='NoColor',
    @Color14 NVARCHAR(10)='NoColor',
    @Color15 NVARCHAR(10)='NoColor',
    @Color16 NVARCHAR(10)='NoColor',
    @Color17 NVARCHAR(10)='NoColor',
    @Color18 NVARCHAR(10)='NoColor',
    @Color19 NVARCHAR(10)='NoColor',
    @Color20 NVARCHAR(10)='NoColor',
    @DiscountPercentage1 TINYINT =0,
    @DiscountPercentage2 TINYINT =100
   AS
         BEGIN


         SELECT  *
         FROM    tblProduct
         WHERE   ( PrdPrice BETWEEN @Price1 AND @Price2 )
                 AND 
                 ( PrdOffPercentage BETWEEN @DiscountPercentage1 AND @DiscountPercentage2 )
                 AND PrdColor IN ( @Color1, @Color2, @Color3, @Color4, @Color5,
                                   @Color6, @Color7, @Color8, @Color9, @Color10,
                                   @Color11, @Color12, @Color13, @Color14,
                                   @Color15, @Color16, @Color17, @Color18,
                                   @Color19, @Color20 )
                 AND PrdSize IN ( @Size1, @Size2, @Size3, @Size4, @Size5,
                                  @Size6, @Size7, @Size8, @Size9, @Size10,
                                  @Size11, @Size12, @Size13, @Size14, @Size15,
                                  @Size16, @Size17, @Size18, @Size19, @Size20 )
                 AND (@CategoryId is null or PrdCategoryId = @CategoryId)

      end

致电：

Exec [dbo].[GetProductByCustomization] @Size1='10',@CategoryId='Cat1'