Multi-condition query and paging Stored Procedure (reverse and sequential query)

Simply paste the code

Create procedure [DBO]. searchbykey

@ Iskeyword varchar (100 ),
@ Iskf varchar (100 ),
@ Isdy varchar (100 ),
@ Iszj varchar (100 ),
@ Lr_begin varchar (100 ),
@ Lr_end varchar (100 ),
@ Iszt varchar (50 ),
@ Islx varchar (100 ),
@ Isly varchar (100 ),
@ Pagesize int, -- page size
@ Pageindex int -- page number
As
Declare

@ Sqlsel varchar (1000 ),
@ Strsql varchar (1000)

Set @ sqlsel = + 'select * From sickinfo where 1 = 1'

If (@ iskeyword! = '')
Set @ sqlsel = isnull (@ sqlsel, '') + 'and askedinfo like ''' +' % '+ @ iskeyword +' % '''

If (@ iskf! = '')
Set @ sqlsel = isnull (@ sqlsel, '') + 'and Kefu = ''' + @ iskf + ''''
If (@ isdy! = '')
Set @ sqlsel = isnull (@ sqlsel, '') + 'and admit = ''' + @ isdy + ''''
If (@ iszj! = '')
Set @ sqlsel = isnull (@ sqlsel, '') + 'and zhuanjia = ''' + @ iszj + ''''
If (@ lr_begin! = ''And @ lr_end! = '')

Set @ sqlsel = @ sqlsel + 'and addtime between ''' + convert (char (10), convert (datetime, @ lr_begin), 21) + ''' and ''' + convert (char (10), convert (datetime, @ lr_end), 21) + ''''

If (@ iszt! = '')
Set @ sqlsel = isnull (@ sqlsel, '') + 'and state = ''' + @ iszt + ''''
If (@ islx! = '')
Set @ sqlsel = isnull (@ sqlsel, '') + 'and istype = ''' + @ islx + ''''

If (@ isly! = '')
Set @ sqlsel = isnull (@ sqlsel, '') + 'and infofrom = ''' + @ isly + ''''

-- Sort by order
-- Set @ strsql = 'select top '+ STR (@ pagesize) +' * from ('+ @ sqlsel + ') as T1 where ID> (select isnull (max (ID), 0) from (select top '+ STR (@ PageIndex-1) * @ pagesize) + 'id from ('+ @ sqlsel +') as T2 order by ID) as t) order by id'
-- Reverse arrange

If (@ pageindex = 1)
Begin
Set @ strsql = 'select top '+ STR (@ pagesize) +' * from ('+ @ sqlsel +') as TT order by id desc'
-- Set @ strsql = 'select top '+ STR (@ pagesize) +' * from # temp2 order by id desc'

End
Else
Begin
Set @ strsql = 'select top '+ STR (@ pagesize) +' * from ('+ @ sqlsel +') as T1 where ID <(select isnull (min (ID ), 0) from (select top '+ STR (@ PageIndex-1) * @ pagesize) + 'id from (' + @ sqlsel + ') as T2 order by id desc) as t) order by id desc'
End
Exec (@ strsql)
-- Exec @ strsql
Go

Function that calls this stored procedure

Public dataset searchbykey (string strsql, string iskeyword, string iskf, string isdy, string iszj, string lr_begin, string lr_end, string iszt, string islx, string isly, int pagesize, int pageindex)
{
Checkconnection ();
Dataset ds1 = new dataset ();
Try
{
Cmd = new sqlcommand (strsql, con );
Cmd. commandtype = commandtype. storedprocedure;
Sqlparameter [] para = {New sqlparameter ("@ iskeyword", sqldbtype. varchar ),
New sqlparameter ("@ iskf", sqldbtype. varchar ),
New sqlparameter ("@ isdy", sqldbtype. varchar ),
New sqlparameter ("@ iszj", sqldbtype. varchar ),
New sqlparameter ("@ lr_begin", sqldbtype. varchar ),
New sqlparameter ("@ lr_end", sqldbtype. varchar ),
New sqlparameter ("@ iszt", sqldbtype. varchar ),
New sqlparameter ("@ islx", sqldbtype. varchar ),
New sqlparameter ("@ isly", sqldbtype. varchar ),
New sqlparameter ("@ pagesize", sqldbtype. varchar ),
New sqlparameter ("@ pageindex", sqldbtype. varchar )};
Para [0]. value = iskeyword;
Para [1]. value = iskf;
Para [2]. value = isdy;
Para [3]. value = iszj;
Para [4]. value = lr_begin;
Para [5]. value = lr_end;
Para [6]. value = iszt;
Para [7]. value = islx;
Para [8]. value = isly;
Para [9]. value = pagesize;
Para [10]. value = pageindex;
Cmd. Parameters. addrange (para );
Sqldataadapter DAP = new sqldataadapter ();
Dap. selectcommand = cmd;
Dap. Fill (ds1, "sickinfo ");
}
Catch (exception E)
{
E. Message. tostring ();
}
Finally
{
Con. Close ();
}
Return ds1;
}