Two ways to write the procedure, discuss which is the good one

Suppose that we have a table name [Article]:

sometimes we want to select top 10 rows where the [Hot] is true or select top 5 rows where the [Elite] is true and sometimes we expect both the [Hot] and [Elite] are true…

Then what should we do?

The bad way is to write three procedures to fit all these situations

Another way maybe like that:

/*In the DAL*/

public static IDataReader List_Data(bool Hot,bool Elite,int PageSize)

{

SqlHelper.ExecuteReader(ConnectionString, "Article_List", GetNull(Hot), GetNull(Elite),GetNull(PageSize));

}

/* the procedure */

ALTER PROCEDURE dbo.Article_List

               (@Hot bit,

                @Elite bit,

                @PageSize int,

                @SortField varchar(50))

AS

        IF (@PageSize>0)

        BEGIN

                SET ROWCOUNT @PageSize   //return 10 or 5 rows depend on the params @PageSize

        END

        SELECT ID,Title

        FROM Article

        WHERE

            (Hot=@Hot OR @Hot=NULL)  //if you want to get data by Hot,then set @Hot=true 

        AND (Elite=@Elite OR @Elite=NULL)

        ORDER BY

                CASE @SortField          

                        WHEN 'Hits' THEN Hits  //sort by the Hit point

                        ELSE AddDate  //sort by date

                END DESC

        RETURN

------------------------------------------------------------------------------

this work no problems but if we want to add another column look like:

Then in order to run the application,we must

1、  add a new column to the DB table

2、  edit the DAL method (add one param)

3、  edit the procedure

so bad the idea!

Then I have found another way ,first create two table :

You can notice that, at the first table

1 means “Hot’

3 means “Hot”+”Elite” 1+2

8 means “Another two”

15 means “Hot”+”Elite”+”Another”+”Another two” 1+2+4+8

…….

So we can write the procedure easily:

ALTER PROCEDURE dbo. Article_List

        @SelectIndex int

AS

        SELECT ID,Title

        FROM Article

        WHERE

             SelectIndex=@SelectIndex           

        RETURN  

-----------------------------------------

compare to the first procedure,

no need to add new column to the [Article] table,we just add one record to the second table

no need to edit the DAL method and the Procedure

we can add the Index to the DB table column [SelectIndex] so it run quickly

at last be care  that ,the SelectIndexID must be 1,2,4,8,16,32,64,128…..

in this case ,we can mix up all the elements with no repeat

maybe someone has another good idea?

as long as there are a  limited number of attributes, your second solution using bit patterns is preferable. But for querying you need boolean expressions e.g.

SELECT * FROM Articles WHERE SelectIndex AND @SelectIndex <> 0

if you need attributes other than boolean, this approch won't work. For dynamic table structures, there are serialized approaches, like UserDefinedTable and User Profiles use to store data.

in fact,if i want to select all the "hot" rows, the @SelectIndex=1 ,but not "selectIndex=1,3,5,9...)

3 equals "Hot"+"Elite" and 5 equals "Hot"+"Another", 9 equals "Hot"+"another two"