Here, I thank the brothers who have passed the related topic posts on all 9CBS blogs. The following method is based on the seven-seven-related sub-storage procedures on the 9CBS blog.
When studying the paging for some time, I saw a brother writing on the page stored procedure on the 9CBS blog, and found that this stored procedure was used. The following is the original paging store process.
- Start Create Procedure GetRecordFromPage ??? @TBLNAME ????? varchar (255), ?????????? @fldname ????? varchar (255), ???? ?? - Field name ??? @PageSize ????? INT = 10, ??????????-page size ??? @PageIndex ??? INT = 1, ????? ?????? - Page code ??? @iscount ????? bit = 0, ???????????? - Return the total number of records, non-0 values return ??? @ORDERTYPE ??? bit = 0, ??????????? - Set the size of the sort type, descending order ??? @strwhere ???? varchar (1000) = ''? - Query Conditions (Note: Don't add WHERE) AS
DECLARE? @STRSQL ?? varchar (1000) ???? - Main statement declare @STRTMP ?? varchar (300) ???? - Temporary variable declare @strorder varchar (400) ?????? Sort type
If @ORDERTYPE! = 0BEGIN ??? set @strtmp = "<(SELECT MIN" ??? set @strorder = "Order by [" @fldname "] desc" endelsebegin ??? set @strtmp = "> SELECT MAX "??? set @strorder ="] ASC "END
Set @STRSQL = "SELECT TOP" STR (@PageSize) "* from [" ??? @tblname "] where [" @fldname "]" @strtmp "(["???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? @fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "[" ??? @fldname "] from [" @TBLNAME "]" @ StrORDER ") AS TBLTMP" ??? @strorder
If @strwhere! = '??? set @strsql = "SELECT TOP" STR (@PageSize) "* from [" ??????? @tblname "] where [" @fldname "]" @STRTMP "(" ??????? @fldname "]) from (SELECT TOP" STR ((@ pageindex-1) * @ Pagesize) "[" ??? ???? @fldname "] from [" @tblname "] where" @strwhere "" ??????? @strorder ") AS TBLTMP) and" @strwhere " " @strorderif @PageIndex = 1begin ??? set @strtmp =" "??? if @strwhere! = '??????? set @strtmp =" where " @strwhere
??? set @strsql = "SELECT TOP" STR (@PageSize) "* from [" ??????? @tblname "]" @strtmp "" @strorderend
IF @iscount! = 0 ??? set @strsql = "Select count (*) as total from [" @tblname "]"
EXEC (@strsql) GO - end
When I was using this stored procedure, I didn't find a problem. Later, when I was very complicated, I found that this stored procedure enforcement error, the following is the condition ID <> 0 and (Companyenname Like) '% shenzhen%' or companychname Like '% shenzhen%' or memo like '% shenzhen%'? or address like '% shenzhen%')? Order by [id] dec) AS TBLTMP) AND ID <> 0 and (Companyenname Like '% shenzhen%' or companychname limited '% shenzhen%' or memo like '% shenzhen%'? or address like '% shenzhen%') and salesid = 9 This condition is no problem, but the above stored procedure is executed, but the report is wrong later, after the debug, output generation SQL statement, found that the original problem is in the nested SQL statement Used (), so, I changed the stored procedure to the following effect, and finally excluded the bug, the following stored procedure, no matter how complicated your conditions, as long as the format is correct, you can run create procedure getRecordFromPage ??? @tblname ????? virchar (255), ?????? - Table name ??? @fldname ????? varchar (255), ?????? - field name ??? @pagesize ???? INT = 10, ??????????-page size ??? @PageIndex ??? INT = 1, ???????????-page code ?? @Iscount ????? bit = 0, ???????????-return to the total number of records, non-0 values return ??? @ORDERTYPE ??? bit = 0, ???? ??????? - Set the type of sort, descending order ??? @strwhere ???? varchar (1000) = ''? - Query Conditions (Note: Don't add WHERE) AsDeclare? @Strsql ?? VARCHAR (1000) ???? - primary sentence DECL Are @Strtmp ?? varchar (300) ???? - Temporary Variable Declare @strORDER VARCHAR (400) ?????? - Sort Type
If @ORDERTYPE! = 0BEGIN ??? set @strtmp = "<(SELECT MIN" ??? set @strorder = "Order by [" @fldname "] desc" endelsebegin ??? set @strtmp = "> SELECT MAX "??? set @strorder ="] ASC "END
Set @STRSQL = "SELECT TOP" STR (@PageSize) "* from [" ??? @tblname "] where [" @fldname "]" @strtmp "(["???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? @fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "[" ??? @fldname "] from [" @TBLNAME "]" @ StrORDER ") AS TBLTMP" ??? @strorderif @Strwhere! = '' ??? set @strsql = "SELECT TOP" STR (@PageSize) "* from [" ??????? @TBLNAME "] Where [" @fldname "]" @strtmp "([" ??????? @fldname "]) from (SELECT TOP" Str ((@PageIndex- 1) * @ PageSize) "[" ??????? @fldname "] from [" @TBLNAME "] Where (" @strwhere ")" ??????? @strorder ") as tbltmp) and (" @Strwhere ")" @strorder
IF @PageIndex = 1begin ??? set @strtmp = "" ??? if @strwhere! = '' ??????? set @strtmp = "Where (" @strwhere ")"
??? set @strsql = "SELECT TOP" STR (@PageSize) "* from [" ??????? @tblname "]" @strtmp "" @strorderend
IF @iscount! = 0 ??? set @strsql = "Select count (*) as total from [" @tblname "]"
EXEC (@strsql) Go
It should be noted that the modified stored procedure is using @Strwhere, it has been added before and after it (), so that nesting () occurs errors.
The following code is an example of the stored procedure SqlConnection myconnection = new sqlconnection (configurationSettings.appsettings ["DSN"]); ??? Dataset mydataset = new dataset (); ??? string strkeyword = keyword.Text.trim ) .Replace ("/ '", "/' / '"); ??? String strsaSid = sales.selectedItem.Value; ??? int recordcount = CalcRecordCount (); ??? RecordNumber.Text = RecordCount.toString ); ??? lblrecordnumber.text = RecordCount.toString (); ??? string strexpress = "ID <> 0"; ??? if (strkeyword! = ") ???? STREXPRESS = strexpress " and (Companyenname like '% " strKeyword "%' or companychname like '% " strKeyword "%' or Companyshortname like '% " strKeyword "%' or web like '% " strKeyword "%' or mainproduct like '% " strKeyword "% 'or Phone Like'%" strkeyword "% 'or memo like'%" strkeyword "% 'or address like'%" strkeyword "% 'or linemanphone like'%" strkeyword "% ') ??? IF (strsalesid! = ") ???? strexpress = strexpress " and salesid = " strsalesid; ?????? sqlcommand mycommand = new sqlcommand (); ??? myCommand.connection = myconnection ;? ?? mycommand.commandtext = "getRecordfromPage"; ??? M Ycommand.comMandType = commandType.StoredProcedure; ??? mycommand.parameters.add ("@ tblname", "customerview"); ??? mycommand.parameters.add ("@ fldname", "id"); ??? mycommand .Parameters.add ("@ strwhere", strexpress; ??? mycommand.parameters.add ("@ pagesize", int32.parse ()));
??? mycommand.parameters.add ("@ pageindex", int32.parse (viewState ["pageindex"]. TOSTRING ()) 1); ??? sqldataareader myreader; ??? myconnection.open (); ?? ? MyReader = myCommand.ExecuteReader (); ?????? CustomerList.VirtualItemCount = RecordCount; ??? CustomerList.DataSource = MyReader; ??? CustomerList.DataKeyField = "id"; ??? CustomerList.DataBind (); ??? myReader.close (); ??? myconnection.close (); here, pay attention to the PageIndex variable used by the stored procedure from 1
?
Finally, once again exclude a bug in the process declare? @Strsql ?? varchar (1000) ???? - primary sentence declare @STRTMP ?? varchar (300) ???? - Temporary variable declare @strorder varchar ( 400) ?????? - Sort type change to declare? @Strsql ?? varchar (2000) ???? - primary sentence declare @strtmp ?? varchar (1000) ???? - Temporary Variable Declare @strorder varchar (1000) ?????? - Sort Type
Because I found some of the statements used by some queries in debugging more than 300, just copy the above stored procedure code and join in your database, then call according to the example.
