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), - Table Name @fldname Varchar (255), - Field Name @PageSize Int = 10, - Page Size @PageIndex Int = 1, - Typographic @iscount Bit = 0, - Return the total number of records, non-0 values returns to @ORDERTYPE BIT = 0, - Set the sort type, non-0 value is descended @Strwhere varchar (1000) = '- Query Conditions (Note: Do not add WHERE )
Declare @strsql varchar (1000) - Proficiency 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 = "ORDER By [" @fldname "] ASC "end
Set @STRSQL = "SELECT TOP" Str (@Pagesize) "* from [" @fldname "] 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 metress like '% shenzhen%' or address like '% shenzhen%') Order by [id] desc) AS TBLTMP) and ID) <> 0 and (Companyenname Like '% shenzhen%' or companychname Like '% shenzhen%' or memo like '% shenzhen%' or address like '% shenzhen%') and salesID = 9 It is said that this condition is no problem, but it is used to report the above stored procedure, and it is an error later. After the debug, the output generated SQL statement is found, the original problem is used in nested SQL statements ( Body, so, I changed the stored procedure to the following effect, and finally excluded the bug, the following stored procedures No matter how complicated your conditions, as long as the format is correct, you can run Create Procedure GetRecordFromPage @tblname varchar (255), - Table name @fldname varchar (255), - field name @Pagesize INT = 10, - Page size @PageIndex INT = 1, - Page @iscount bit = 0, - Return the total number of records, non-0 values return @ORDERTYPE BIT = 0, - Set the sort type, non-0 value is descended @Strwhere varchar (1000) = '- Query Conditions (Note: Do not add where) asclare @strsql varchar (1000) - Principal statement 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 = "ORDER By [" @fldname "] ASC "end
Set @STRSQL = "SELECT TOP" Str (@Pagesize) "* from [" @fldname "] 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 reference procedure SqlConnection MyConnection = new SqlConnection (ConfigurationSettings.AppSettings [ "dsn"]); DataSet MyDataSet = new DataSet ();. String strKeyword = Keyword.Text.Trim () Replace ( "/ ' "," /' / ' "); string strSalesId = 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 "%" strkeyword "% 'or phone like'%" strkeyword "% 'or memo like'%" strkeyword "%" or address like '% " strKeyword "%' or linkmanphone 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.); SqlDataReader MyReader; MyConnection.Open (); MyReader = MyCommand.ExecuteReader (); CustomerList.VirtualItemCount = RecordCount; CustomerList.DataSource = MyReader; Customerlist.DataKeyfield = "ID"; customerList.database (); myreader.close (); myconnection.close (); myconnection.close (); here, pay attention to the PageIndex variable used by the stored procedure is from 1 start
Finally, once again exclude a bug to store the Declare @strsql varchar (1000) - the primary sentence DECLARE @strtmp varchar (300) - Temporary Variable Declare @strorder VARCHAR (400) - Sort Type Change to Declare @strsql varchar ( 2000) - Seclare @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.
Create Procedure PageProcedure @ Sqlstr NVARCHAR (4000), - Query Strings @CurrentPage Int, - NAP @Pagesize Int - Variety of rows asset nocount onDeclare @ p1 int, --p1 is the id @Rowcount INTEXEC sp_cursoropen @ P1 output, @ sqlstr, @ scrollopt = 1, @ ccopt = 1, @ rowcount = @ rowcount outputselect ceiling (1.0 * @ rowcount / @ pagesize) as AllPageCount -, @ rowcount as ALLRowCount, @ currentpage as currentPage set @ CurrentPage = (@ currentpage-1) * @ PageSize 1exec sp_cursorfetch @ p1, 16, @ currentpage, @ PageSize Exec sp_cursorclose @ p1set nocount OFF
GO is very simple to use the cursor, but it is very practical.
