Complex search + filter conditions in the database + sorting + pagination

Complex in-database search + filter conditions + sorting + paging has always been a headache.

To simulate this problem, first set up two tables

CREATE TABLE T_usertype (    ID int identity () not null primary key,    name varchar ()) Gocreate table T_user (    ID int Identity (primary) is not NULL    , T_usertypeid int is not NULL,    name varchar,    foreign KEY (T_usertyp eId) references T_usertype (ID)) GO

Insert some test data below

　　

In the T_user table, the T_usertypeid field is associated to the T_usertype table

We want to query the user, while querying the name of this user type, can be implemented by the search

Select U.*, T.name as typeName from    t_user u    inner join T_usertype t on    t.id = U.t_usertypeid

If you have more tables, you will be more complex, so create a view

Create View View_user_andtypeas    Select U.*, t.name as typeName from    t_user u    inner join t_usertype t on    T. id = U.t_usertypeidgo

At this point, using the following statement, we can get the results we want.

SELECT * FROM View_user_andtype

If you want to provide paging functionality, you need to write this

Select Top 5 * from View_user_andtype where id ' not ' in (SELECT ID top 0 view_user_andtype)

Adding conditional filtering and sorting

Select Top 5 * from View_user_andtype where id>1 and     ID not in (        select top 0 ID view_user_andtype         where ID >1 ORDER BY ID) Order by ID

If each table is written in the same way, it's a big one.

So through a stored procedure, encapsulate paging and sorting logic

--Stored procedure: General Paging----paging query a table or view----parameter list:--srctablename: View or table name--idcolumnname: Primary key Column Name--pageSize: Length per page (1~n)-- PageIndex: Page number (1~n)--Condition: Filter Condition-ORDER by: Sort by, must be the field name in the query result--ISDESC: Reverse, Optional value (True, false) --set QUOTED_IDENTIFIER on Goset ansi_nulls on GOif object_id (' proc_selectbypage ', ' P ') are not NULL Drop PROCEDURE [Proc_selectbypage]; Gocreate procedure [dbo]. [Proc_selectbypage] @srcTableName varchar, @idColumnName varchar = ' id ', @pageSize int = ten, @pageInde    x int = 1, @condition varchar (+) = ", @orderBy varchar, @isDesc varchar = ' false ' Asbegin--parameter fault tolerance if (@pageIndex <= 0) begin Set @pageIndex = 1 End--ASSEMBLY statement DECLARE @sql1 varchar (4000) Set @sql1  = ' Select top ' + CAST (@pageSize as varchar) + ' * from ' + @srcTableName + ' WHERE (' + @idColumnName + ' not in (select top ' + CAST ((@pageSize * (@pageIndex-1)As varchar) + ' + @idColumnName + ' from ' + @srcTableName if (@condition <> ") begin set @sql1 = @sql1 + ' where ' + @condition end s ET @sql1 = @sql1 + ' ORDER BY ' + @orderBy if (@isDesc = ' true ') begin set @sql1    = @sql1 + ' desc ';    End else if (@isDesc = ' false ') begin set @sql1 = @sql1 + ' ASC '; End Set @sql1 = @sql1 + ') ' if (@condition <> ') begin set @sql1 = @sql1 + ' and ' + @cond Ition End Set @sql1 = @sql1 + ') ' Set @sql1 = @sql1 + ' ORDER BY ' + @orderBy if (@isDesc = ' true    ') begin set @sql1 = @sql1 + ' desc ';    End else if (@isDesc = ' false ') begin set @sql1 = @sql1 + ' ASC '; End-output statement and execute print @sql1 exec (@sql1) endgoset quoted_identifier OFF goset ANSI_NULLS on GO

You can write the same function again.

exec proc_selectbypage ' view_user_andtype ', ' ID ', 3, 2, ', ' name ', ' false '

Can be compatible with tables or views of pagination, sqlserver2000 under test

Original address: http://www.cnblogs.com/S-E-P/archive/2012/06/10/2543970.html

Small ant products in different: The main product, the product under a number of specifications first with the main product paging, and then more master Product ID collection and then find sub-products, and finally the association with the Code implementation

Complex search + filter conditions in the database + sorting + pagination