How to make a store procedure that filter all record by some condition

Question

Hello everyone I am facing a problem in making a store procedure ,I want make a stored procedure like that in which in have forty four type of input parameter in which 20 is for size1 to size20 and 20 for color Color1-color20 and two for price two for percentage and i want i filter the record according to the parameter my stored procedure code is fallowing

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

And i want a stored procedure in which if i enter one value for one parameter or all value for all parameter it give me result if one or more than one parameter is matching in the table

This is a classic scenario for using table valued parameters. — Zohar Peled, Oct 08 '18 at 13:29
Tip: Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on posting database questions. The volunteers you are asking to help you don't really need to wade through 20 colors and 20 sizes. Some of us can see a pattern beginning at only 15. Or two. You may also want to read about [Dynamic Search Conditions in T-SQL](http://www.sommarskog.se/dyn-search.html). — HABO, Oct 08 '18 at 13:55

score 0 · Answer 1 · answered Oct 08 '18 at 13:37

Like the other answer says, look into using Arrays as parameters. If you want to go with this, then set a default for your parameters for either null or something you are sure not to make a hit on, or the full spectrum(0-100 in percentages for example)

  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

Call by:

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

Sir its not workng ,not giving any record – AkshayTomar Oct 08 '18 at 19:16 — AkshayTomar, Oct 08 '18 at 19:16

How to make a store procedure that filter all record by some condition

1 Answers1