The paging stored procedures are roughly the following
1. Use not in and select top
2. Use ID greater than number and select top
3, using the cursor in SQL
4. Temporary table
You can see the following links on the web
Summary of common paging stored procedures in C #
Http://read.newbooks.com.cn/info/174545.html
In 2005 we have a lot of choices, we can take advantage of the new syntax CTE (common table expression), the introduction of CTE you can see a series of tutorials in the blog park
Http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html
or simply on the website of Microsoft
http://msdn.microsoft.com/zh-cn/library/ms190766 (sql.90). asp Tutorials X
View specific content.
In addition, you can take advantage of some of the new functions that are added in 2005: Row_number (), Rank,dense_rank,ntile, which you can use to efficiently analyze data and provide sorted values to query hungry result rows. You might find typical scenarios where these new functions are useful include: assigning consecutive integers to result rows for presentation, paging, scoring, and drawing histograms.
For more information, see the following links
Http://blog.111cn.net/htl258/archive/2009/03/20/4006717.aspx
My main use here is Row_number () with the new syntax CTE, first affixed to my stored procedures. Design, development, test stored procedures and related C # code will take me two days, but the following similar interface is very fast, a morning can be two pages of page display, even if the complex query, can be done in a morning.
The following stored procedures do not return the total number of pages and total entries, if you are interested, you can add them yourself, see the following sections of the common paging stored procedure summary in C #
Declare @sql nvarchar (4000);
Declare @totalRecord int;
--Calculate the total number of records
if (@SqlWhere = ' ' or @SqlWhere = ' ' or @sqlWhere is NULL)
Set @sql = ' Select @totalRecord = count (*) from ' + @TableName
Else
Set @sql = ' Select @totalRecord = count (*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql, N ' @totalRecord int OUTPUT ', @totalRecord output--total number of records calculated
--Calculate Total pages
Select @TotalPage = @totalRecord--ceiling (@totalRecord +0.0)/@PageSize)
Stored procedure SQL is as follows, supports indefinite column, indefinite condition, multiple table union, sort arbitrary
Copy code code as follows:
Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go
--declare @sql nvarchar (4000);
--declare @totalRecord int;
----Calculate the total number of records
--if (@SqlWhere = ' "' or @SqlWhere = ' ' or @sqlWhere is NULL)
--set @sql = ' Select @totalRecord = count (*) from ' + @TableName
--else
--set @sql = ' Select @totalRecord = count (*) from ' + @TableName + ' where ' + @sqlWhere
--exec sp_executesql @sql, N ' @totalRecord int OUTPUT ', @totalRecord output--total number of records calculated
--
----Calculate Total Pages
--
--select @TotalPage = @totalRecord--ceiling (@totalRecord +0.0)/@PageSize)
-- =============================================
--Author:shiwenbin
--Msn:jorden008@hotmail.com
--Email:jorden008@163.com
--Create date:2009-10-20
--Description: A paging stored procedure that returns the results of pagination based on parameters passed
--Parameters:
-- =============================================
ALTER PROCEDURE [dbo]. [Proc_getdatapaged]
--Add The parameters for the stored procedure
@StrSelect varchar (max) =null--Columns to display (multiple columns separated by commas), for example: Id,name
@StrFrom varchar (max) = null, table name, or table connection string, multiple table joins for example: student as S inner join Dwinfo as DW on S.DWBH=DW.BH
@StrWhere varchar (max) =null, the query condition, ' represents no condition, single condition or multiple conditions, multiple conditions such as: Name= ' ah ' and id=10
@StrOrder varchar (max) =null--row sequence (multiple rows separated by commas), such as: ID Desc,name as
--@PageCount int output,--Total pages
@ItemCount bigint output,--Total record number
@PageSize int = 50--Displays the number of bars per page
@BeginIndex Int=1,--The number of records started
@DoCount bit = 0-whether statistic totals, 0 not statistics, 1 statistics
--@PageIndex int = 1--current page
--@ClassCode char (=null)--unit Number (class number)
As
BEGIN
SET NOCOUNT on;
Declare @sql nvarchar (4000);
Declare @totalRecord int;
--Calculate the total number of records
if (@StrWhere = ' ' or @StrWhere = ' ' or @StrWhere is NULL)
Set @sql = ' Select @totalRecord = count (*) from ' + @StrFrom
Else
Set @sql = ' Select @totalRecord = count (*) from ' + @StrFrom + ' where ' + @StrWhere
EXEC sp_executesql @sql, N ' @totalRecord int OUTPUT ', @ItemCount output--total number of records calculated
DECLARE @SqlQuery varchar (max)
--if (@PageIndex =1)
if (@BeginIndex =1 or @BeginIndex =0 or @BeginIndex <0)
Begin
if (@StrWhere is null)--if (@StrWhere = ')
Set @SqlQuery = ' Select top ' +convert (varchar, @PageSize)
+ ' row_number (order by ' + @StrOrder + ') as RowNumber, ' + @StrSelect +
' From ' + @StrFrom;
Else
--set @sql = ' SELECT Top @PageSize * The @TableName ORDER by id DESC ';
--select Top @PageSize * The @TableName ORDER by id DESC;
Set @SqlQuery = ' Select top ' +convert (varchar, @PageSize)
+ ' row_number (order by ' + @StrOrder + ') as RowNumber, ' + @StrSelect + ' from ' + @StrFrom + ' where ' + @StrWhere;
--exec (@SqlQuery)
--@SqlQuery
End
Else
Begin
if (@StrWhere is null)--if (@StrWhere = ')
Begin
Set @SqlQuery = ' with CTE as (
Select Row_number () over (order by ' + @StrOrder + ') as RowNumber, ' + @StrSelect + ' from ' + @StrFrom + '
)
SELECT * from CTE where rownumber between ' +
--convert (varchar, (@PageIndex-1) * @PageSize) +1) + ' + ' +
--
--CONVERT (varchar, @PageIndex * @PageSize)
CONVERT (varchar, @BeginIndex) + ' and ' +
CONVERT (varchar, @BeginIndex + @PageSize)
--print @SqlQuery
End
Else
Begin
Set @SqlQuery = ' with CTE as (
Select Row_number () + @StrOrder + ') as RowNumber, ' + @StrSelect + ' from ' + @StrFrom + ' where ' + @StrWhere + '
)
SELECT * from CTE where rownumber between ' +
--convert (varchar, (@PageIndex-1) * @PageSize) +1) + ' + ' +
--
--CONVERT (varchar, @PageIndex * @PageSize)
CONVERT (varchar, @BeginIndex) + ' and ' +
CONVERT (varchar, @BeginIndex + @PageSize)
--print @SqlQuery
End
End
--set @SqlQuery = @SqlQuery + '; select @ItemCount =count (*) from ' + @TableName
--set @PageCount = @ItemCount/@PageSize
--print ' Total ' + @PageConut + ' page ' + @ItemCount + ' strip '
--print @ItemCount
Print @SqlQuery
EXEC (@SqlQuery)
End
C # related Code database tutorial Access is Microsoft's corporate library V4.1
Enterprise Library 4.1 Download Address:
http://www.microsoft.com/downloads/details.aspx?FamilyId=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en
Sample code, foreground page, foreground for user control
Copy code code as follows:
<%@ control language= "C #" autoeventwireup= true "codebehind=" StudentDetailsTable.ascx.cs "inherits=" Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl.StudentDetailsTable "%>
<link href= ". /css Tutorial/core.css "rel=" stylesheet "type=" Text/css "/>"
<table class= "Studentpagingtablepanel" >
<tr>
<td> Unit: <asp:label id= "Lblclassname" runat= "Server" text= "Label" ></asp:Label></td>
<td> level: <asp:label id= "Lblclasslevel" runat= "Server" text= "Label" ></asp:Label> level node </td>
</tr>
<tr>
<td> The unit has a total of <asp:label id= "Lblstudenttype" runat= "Server" text= "Label" ></asp:Label> student
<asp:label id= "Lblstudentcount" runat= "Server" text= "Label" ></asp:Label> person </td>
<td> each page displays <asp:dropdownlist id= "ddlpagesize" runat= "Server" autopostback= "True"
onselectedindexchanged= "Ddlpagesize_selectedindexchanged" >
</asp:DropDownList> people <asp:label id= "Lblpagecount" runat= "Server" text= "Label" ></asp:Label> page
Now is <asp:label id= "Lblpageindex" runat= "Server" text= "Label" ></asp:Label> page
<asp:linkbutton id= "FirstPage" runat= "Server" commandargument= "a"
oncommand= "Linkbutton_command" > Home </asp:LinkButton>
<asp:linkbutton id= "NextPage" runat= "Server" commandargument= "Next"
oncommand= "Linkbutton_command" > next page </asp:LinkButton>
<asp:linkbutton id= "PrevPage" runat= "Server" commandargument= "prev"
oncommand= "Linkbutton_command" > Prev </asp:LinkButton>
<asp:linkbutton id= "LastPage" runat= "Server" commandargument= "last"
oncommand= "Linkbutton_command" > Last </asp:LinkButton>
</td>
</tr>
</table>
<br/>
<asp:gridview id= "gvstudent" runat= "Server" autogeneratecolumns= "False"
emptydatatext= "No qualifying Data" >
<Columns>
<asp:templatefield headertext= "Photo" >
<ItemTemplate>
<asp:image id= "Image1" cssclass= "Studentimage" ImageUrl =<%# (Eval ("Getstudentimageurl") zpadress%> " Server "/>
</ItemTemplate>
</asp:TemplateField>
<asp:templatefield headertext= "name (in English and Chinese)" >
<ItemTemplate>
<asp:label id= "Label1" runat= "server" text= ' <%# Eval ("XMJZ")%> ' ></asp:Label>
<br/>
<asp:label id= "Label2" runat= "server" text= ' <%# Eval ("Xmjy")%> ' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:boundfield datafield= "JX" headertext= "Rank"/>
<asp:boundfield datafield= "ZW" headertext= "Job"/>
<asp:boundfield datafield= "The headertext=" and "the Country"/>
<asp:boundfield datafield= "Sjyqk" headertext= "educational level"/>
<asp:boundfield datafield= "ZJ" headertext= "Religion"/>
<asp:templatefield Headertext= "born/Enlisted" >
<ItemTemplate>
<asp:label id= "Label3" runat= "server" text= ' <%# setbirthdate (Eval ("Csrq"))%> ' ></asp:Label>
<br/>
<asp:label id= "Label4" runat= "server" text= ' <%# setenrolldate (Eval ("RWRQ"))%> ' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:boundfield datafield= "Xzz" headertext= "room/floor number"/>
<asp:templatefield headertext= "Telephone/trumpet" >
<ItemTemplate>
<asp:label id= "Label5" runat= "server" text= ' <%# Eval ("DHD")%> ' ></asp:Label>
<br/>
<asp:label id= "Label6" runat= "server" text= ' <%# Eval ("DHX")%> ' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:boundfield datafield= "FCJP" headertext= "return ticket"/>
<asp:boundfield datafield= "XH" headertext= "School Number"/>
</Columns>
</asp:GridView>
Sample code, background code
Copy code code as follows:
Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Web;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Text;
Using System.Data;
Using System.Data.Common;
Using Microsoft.Practices.EnterpriseLibrary.Common;
Using Microsoft.Practices.EnterpriseLibrary.Data;
Using Kimbanx.UCS.ForeignStudentAdmin.Model;
Using Kimbanx.UCS.ForeignStudentAdmin.Common;
Namespace Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl
{
public partial class StudentDetailsTable:System.Web.UI.UserControl
{
Private Database _db = Databasefactory.createdatabase ();
Private DbCommand _command;
Private DbConnection _connection;
Private DataSet _ds;
private string _classcode;
private string _classfullname;
private string _studenttype;
private string _studentcount;
private string _querystringwhere;
Private DataTable _studenttable;
Protected string Setbirthdate (Object obj)
{
string result = String. Empty;
String temp = obj. ToString ();
result = DateTime.Parse (temp). ToShortDateString ();
return result;
}
Protected string Setenrolldate (Object obj)
{
string result = String. Empty;
String temp = obj. ToString ();
result = DateTime.Parse (temp). ToShortDateString ();
return result;
}
protected void Filldata_dllpagesize ()
{
for (int i = 1; i < i++)
{
DDLPAGESIZE.ITEMS.ADD (i.ToString ());
}
Ddlpagesize.selectedindex = 14;
}
protected void Initsession ()
{
session["PageSize"] = 0;
session["PageIndex"] = 1;
session["PageCount"] = Int. Parse (_studentcount)/15 + 1;
}
<summary>
Get QueryString Pass Parameters
</summary>
protected void Getquerystringpara ()
{
_classcode = request.querystring["DWBH"];
_classfullname =httputility.urldecode (request.querystring["DWMC"));
_studentcount = request.querystring["Studentcount"];
_studenttype =httputility.urldecode (request.querystring["Studenttype"));
_querystringwhere = request.querystring["where"];
}
protected void Setlabeltext ()
{
This.lblClassName.Text = _classfullname;
This.lblClassLevel.Text = GetClassInfo (_classcode). Level.tostring ();
This.lblStudentCount.Text = _studentcount;
This.lblStudentType.Text = _studenttype;
}
#region
<summary>
Get Student data
</summary>
<param name= "Strselect" > Displayed fields </param>
<param name= "Strfrom" > </param>
<param name= "strwhere" > Query conditions </param>
<param name= "PageSize" > per-page display number of bars </param>
<param name= "PageIndex" > Current page </param>
<returns></returns>
Protected DataTable getstudentdata (string strselect,string strfrom,string strwhere,int pagesize,int pageIndex)
//{
_command = _db. Getstoredproccommand ("studentpaging");
_db. Addinparameter (_command, "Strselect", dbtype.string, "ZPADRESS,XMJZ,XMJY,JX,ZW,GJ,SJYQK,ZJ,CSRQ,RWRQ,XZZ,DHD,DHX, Fcjp,hzh,xh ");
_db. Addinparameter (_command, "Strfrom", dbtype.string, "Tx_xyzl");
_db. Addinparameter (_command, "strwhere", dbtype.string, strwhere);
_db. Addinparameter (_command, "Strorder", dbtype.string, "id");
_db. Addinparameter (_command, "PageSize", Dbtype.int32, PageSize);
_db. Addinparameter (_command, "PageIndex", Dbtype.int32,pageindex);
_studenttable = _db. ExecuteDataset (_command). Tables[0];
return _studenttable;
//}
#endregion
Protected string Getstudentimageurl (object IMAGEURL)
{
string ServerURL = Http://192.168.0.1/admin;
String ImageUrl = String. Empty;
if (!) ( IMAGEURL = = null))
{
String temp = Imageurl.tostring (). Trim ();
if (!string. IsNullOrEmpty (temp))
{ImageUrl = string. Format ("{0}{1}", ServerURL, temp.) Substring (temp. IndexOf ("/")); }
}
return ImageUrl;
}
<summary>
Data after binding pagination
</summary>
<param name= "PageSize" > The amount of data displayed per page </param>
<param name= "PageIndex" > Current page </param>
protected void Bindstudentdata (int pageSize, int pageIndex)
{
Switch (_querystringwhere)
{
Case "JX":
This.gvStudent.DataSource = Helper.studentpagingresult (
"Zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
"Student",
String. Format ("Dwbh= ' {0} ' and jx= ' {1} '", _classcode, _studenttype),
"id",
PageSize,
PageIndex);
This.gvStudent.DataBind ();
Break
Case "the":
This.gvStudent.DataSource = Helper.studentpagingresult (
"Zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
"Student",
String. Format ("Dwbh= ' {0} ' and gj= ' {1} '", _classcode, _studenttype),
"id",
PageSize,
PageIndex);
This.gvStudent.DataBind ();
Break
Case "Allyear":
This.gvStudent.DataSource = Helper.studentpagingresult (
"S.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
"Student as s inner join class as DW on S.dwbh=dw.bh",
String. Format (@ "s.dwbh= ' {0} ' and" Dw.kxsj>=convert (DateTime, ' {1} ' + ' -01-01 ',) and
Dw.kxsj<=convert (DateTime, ' {1} ' + ' -12-31 ',) or Dw.bysj>=convert (datetime, ' {1} ' + ' -01-01 ',) and
Dw.bysj<=convert (DateTime, ' {1} ' + ' -12-31 ',) ", _classcode, _studenttype),
"S.id",
PageSize,
PageIndex);
This.gvStudent.DataBind ();
Break
Case "NEW":
This.gvStudent.DataSource = Helper.studentpagingresult (
"S.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
"Student as s inner join class as DW on S.dwbh=dw.bh",
String. Format (@ "s.dwbh= ' {0} ' and" Dw.kxsj>=convert (DateTime, ' {1} ' + ' -01-01 ',) and
Dw.kxsj<=convert (DateTime, ' {1} ' + ' -12-31 ',) ", _classcode, _studenttype),
"S.id",
PageSize,
PageIndex);
This.gvStudent.DataBind ();
Break
}
}
protected void Page_Load (object sender, EventArgs e)
{
if (Userauthhelper.getuserauthtype ("1") = = userauthenum.admin| |
Userauthhelper.getuserauthtype ("2") = = Userauthenum.currentstudentdetails)
{
Getquerystringpara ();
Setlabeltext ();
if (getstudentcount () = = 0)
{
Studentcountzero ();
Return
}
if (! IsPostBack)
{
Filldata_dllpagesize ();
Setpageindex (1);
Setpagecount ();
Bindstudentdata (GetPageSize (), Getpageindex ());
}
Else
{
}
}
Else
{
This. Controls.Add ("You do not have the appropriate permission, please contact the Administrator"));
}
}
<summary>
Get class information, class full name, class level
</summary>
<param name= "Classcode" > class number </param>
<returns></returns>
Protected classentity GetClassInfo (string classcode)
{
classentity entity = new classentity ();
Entity. Code = Classcode;
_command = _db. Getstoredproccommand ("ClassInfo");
_db. Addinparameter (_command, "BH", dbtype.string, Classcode);
using (IDataReader reader = _db. ExecuteReader (_command))
{
while (reader. Read ())
{
Entity. FullName = reader. GetString (1);
Entity. Level = reader. GetInt32 (2);
}
}
return entity;
}
#region Get and Set PageSize
protected int getpagesize ()
{
return int. Parse (Ddlpagesize.selectedvalue);
}
protected void setpagesize (int pageSize)
{
This.ddlPageSize.Text = Pagesize.tostring ();
}
#endregion
#region Get and Set PageIndex
protected int Getpageindex ()
{
return int. Parse (This.lblPageIndex.Text.Trim ());
}
protected void Setpageindex (int pageIndex)
{
This.lblPageIndex.Text = Pageindex.tostring ();
}
#endregion
#region Get and Set PageCount
protected int Getpagecount ()
{
return int. Parse (This.lblPageCount.Text.Trim ());
}
protected void Setpagecount ()
{
int studentcount = Getstudentcount ();
int pageSize = GetPageSize ();
if (studentcount% pageSize = 0)
{
This.lblPageCount.Text = (studentcount/pagesize). ToString ();
}
Else
{
This.lblPageCount.Text = (studentcount/pagesize + 1). ToString ();
}
}
#endregion
#region Get and Set Studentcount
protected int Getstudentcount ()
{
return int. Parse (This.lblStudentCount.Text.Trim ());
}
protected void Setstudentcount (int studentcount)
{
This.lblStudentCount.Text = Studentcount.tostring ();
}
#endregion
protected void Studentcountzero ()
{
This.lblPageIndex.Text = "0";
This.lblPageCount.Text = "0";
}
protected void Linkbutton_command (object sender, CommandEventArgs e)
{
if (getstudentcount () = = 0)
{
Studentcountzero ();
Return
}
int pagecount = Getpagecount ();
int pageIndex = Getpageindex ();
int pageSize = GetPageSize ();
Switch (e.commandargument.tostring ())
{
Case "a":
if (PageIndex = = 1) {}
Else
{
PageIndex = 1;
Setpageindex (PageIndex);
PageSize = GetPageSize ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
}
Break
Case "Next":
if (PageCount = = PageIndex & pageIndex = 1)
{ }
else if (PageIndex = 1 && pagecount > PageIndex)
{
Setpageindex (++pageindex);
PageSize = GetPageSize ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
}
else if (PageIndex > 1 && pagecount = = PageIndex)
{ }
Else
{
Setpageindex (++pageindex);
PageSize = GetPageSize ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
}
Break
Case "Prev":
if (PageIndex = 1)
{ }
else if (PageIndex = PageCount && pageIndex > 1)
{
Setpageindex (--pageindex);
PageSize = GetPageSize ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
}
else if (PageIndex = 2)
{
Setpageindex (1);
PageSize = GetPageSize ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
}
Else
{
Setpageindex (--pageindex);
PageSize = GetPageSize ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
}
Break
Case "Last":
if (PageCount = = PageIndex)
{ }
Else
{
Setpageindex (PageCount);
PageIndex = Getpageindex ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
}
Break
Default
Setpageindex (1);
PageSize = GetPageSize ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
Break
}
}
protected void Ddlpagesize_selectedindexchanged (object sender, EventArgs e)
{
int pageIndex = Getpageindex ();
int pagecount = Getpagecount ();
int pageSize = GetPageSize ();
PageIndex = 1;
Setpageindex (PageIndex);
SetPageSize (int. Parse (((DropDownList) sender). SelectedValue));
Pagesize=getpagesize ();
Setpagecount ();
Bindstudentdata (PageSize, PageIndex);
}
}
}
Finally, a round friend of the common stored procedures, original address: Universal stored procedure paging (using Row_number () and do not use Row_number () two scenarios) performance analysis
Copy code code as follows:
Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go
-- =============================================
--Author: <jiangrod>
--Create Date: <2010-03-03>
--Description: <sql2005 and subsequent versions common paging stored procedures > Invocation methods: sp_pager2005 ' xtest ', ' * ', ' ORDER by ID ASC ', ' xname like ' '%222name% ' ' , 2,20,0,0
--for querying data from a single table
-- =============================================
ALTER PROCEDURE [dbo]. [Proc_getdatapaged2]
@tblName varchar (255),--table name such as: ' Xtest '
@strGetFields varchar (1000) = ' * ',--the column that needs to be returned: ' Xname,xdemo '
@strOrder varchar (255) = ',--sorted field name such as: ' ORDER by id DESC '
@strWhere varchar (1500) = ',--Query criteria (note: Do not add where) such as: ' XName like '%222name% '
@beginIndex int=1,--Start recording position
--@pageIndex int = 1,--page number: 2
@pageSize int = 50, number of records per page: 20
@recordCount int output,--Total number of records
@doCount bit=0-not 0 statistics, 0 is not statistical (statistics can affect efficiency)
As
DECLARE @strSQL varchar (5000)
declare @strCount nvarchar (1000)
--Total Record bar number
if (@doCount!=0)
Begin
if (@strWhere!= ')
Begin
Set @strCount = ' Set @num = (select count (1) from ' + @tblName + ' where ' + @strWhere + ') '
End
Else
Begin
Set @strCount = ' Set @num = (select count (1) from ' + @tblName + ') '
End
EXECUTE sp_executesql @strCount, N ' @num INT output ', @RecordCount output
End
If @strWhere!= '
Begin
Set @strWhere = ' where ' + @strWhere
End
Set @strSQL = ' SELECT * FROM (select Row_number () over (' + @strOrder + ') as ROWID, '
Set @strSQL = @strSQL + @strGetFields + ' from [' + @tblName + '] ' + @strWhere
Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str (@beginIndex)
Set @strSQL = @strSQL + ' and ' +str (@beginIndex + @PageSize)
--set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str ((@PageIndex-1) * @PageSize + 1)
--set @strSQL = @strSQL + ' and ' +str (@PageIndex * @PageSize)
EXEC (@strSQL)
One more.
Copy code code as follows:
Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go
-- =============================================
--Author: <Author,,Name>
--Create Date: <create date,,>
--Description: Paging to get commodity information
--calling method: Proc_getproductpaged ' 2 ', ' * ', ', ' xname like '%222name% ', ' ORDER by ID ASC ', 20,2,0,0
-- =============================================
ALTER PROCEDURE [dbo]. [Proc_getproductpaged]
--Add The parameters for the stored procedure
@ProductType smallint=1,--Commodity type, 1 all 2 seeds 3 Pesticides 4 Fertilizers
@StrSelect varchar (max) = ',--display field
@StrFrom varchar (max) = ',--query source
@StrWhere varchar (max) = ',--query criteria
@StrOrder varchar (max) = ',--sort specification
Number of records,--@PageSize int=50
@BeginIndex int=1,--Start recording position
--@PageIndex int=1,--page number
Total @Count int output,--record
@DoCount bit=0--1 Statistics, 0 is not statistical (statistics can affect efficiency)
As
BEGIN
---SET NOCOUNT on added to prevent extra result sets from
--interfering with SELECT statements.
DECLARE @seedtype int
Set @seedtype =2
DECLARE @pestype int
Set @pestype =3
DECLARE @ferttype int
Set @ferttype =4
CREATE TABLE #product
(
ProductID uniqueidentifier,
ProductName varchar (50),
ClassName varchar (50),
ProductType int,
CreateDate datetime,
Modifydate datetime
--CompanyID uniqueidentifier
)
DECLARE @strSQL varchar (max)
declare @strCount nvarchar (max)
--Calculating the total number of record bars
if (@DoCount!=0)
Begin
if (@StrWhere!= ')
Begin
if (@ProductType =1)
Begin
Set @strCount = ' Set @num = (select count (1) from Seed where ' + @StrWhere + ') ' +
' + (select COUNT (1) from pesticide where ' + @StrWhere + ') ' +
' + (select COUNT (1) from fertilizer where ' + @StrWhere + ') '
End
else if (@ProductType =2)
Begin
Set @strCount = ' Set @num = (select count (1) from Seed where ' + @StrWhere + ') '
End
else if (@ProductType =3)
Begin
Set @strCount = ' Set @num = (select count (1) from pesticide where ' + @StrWhere + ') '
End
else if (@ProductType =4)
Begin
Set @strCount = ' Set @num = (select count (1) from fertilizer where ' + @StrWhere + ') '
End
End
Else
Begin
if (@ProductType =1)
Begin
Set @strCount = ' Set @num = (select count (1) from Seed) ' +
' + (select COUNT (1) from pesticide) ' +
' + (select COUNT (1) from fertilizer) '
End
else if (@ProductType =2)
Begin
Set @strCount = ' Set @num = (select count (1) from Seed) '
End
else if (@ProductType =3)
Begin
Set @strCount = ' Set @num = (select count (1) from pesticide) '
End
else if (@ProductType =4)
Begin
Set @strCount = ' Set @num = (select count (1) from fertilizer) '
End
End
EXECUTE sp_executesql @strCount, N ' @num INT output ', @Count output
End
--Paging to get data
if (@StrWhere!= ')
Begin
Set @StrWhere = ' where ' + @StrWhere
End
if (@ProductType =1)
Begin
Set @strSQL = ' INSERT INTO #product
Select S.seedid,s.seedname,cc.cropclassname, ' +cast (@seedtype as varchar (1)) + ', s.createdate,s.modifydate
From seed as s inner join Cropclass as CC on S.cropclasscode=cc.cropclasscode '
+ @StrWhere +
' Union
Select P.pesticideid,p.pesname,pc.pesclassname, ' +cast (@pestype as varchar (1)) + ', p.createdate,p.modifydate
From pesticide as P inner join pesclass as PC on P.pesclasscode=pc.pesclasscode '
+ @StrWhere +
' Union
Select F.fertilizerid,f.fertname,fc.fertclassname, ' +cast (@ferttype as varchar (1)) + ', f.createdate,f.modifydate
From fertilizer as F inner join Fertilizerclass as FC on F.fertclasscode=fc.fertclasscode '
+ @StrWhere
Set @strSQL = @strSQL + ' SELECT * FROM (select Row_number () over (' + @StrOrder + ') as ROWID, '
Set @strSQL = @strSQL + ' * from [#product] '--+ @StrWhere
Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str (@BeginIndex)
Set @strSQL = @strSQL + ' and ' +str (@BeginIndex + @PageSize-1)
--Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str ((@PageIndex-1) * @PageSize + 1)
--Set @strSQL = @strSQL + ' and ' +str (@PageIndex * @PageSize)
End
Else
Begin
Set @strSQL = ' INSERT into #product select ' + @StrSelect +
' From ' + @StrFrom + @StrWhere
EXEC (@strSQL)
Set @strSQL = '
Set @strSQL = ' SELECT * FROM (select Row_number () over (' + @strOrder + ') as ROWID, '
Set @strSQL = @strSQL + ' * from [#product] ' + @strWhere
Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str (@BeginIndex)
Set @strSQL = @strSQL + ' and ' +str (@BeginIndex + @PageSize-1)
--Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str ((@PageIndex-1) * @PageSize + 1)
--Set @strSQL = @strSQL + ' and ' +str (@PageIndex * @PageSize)
End
-Else if (@ProductType =2)
--Begin
--Set @strSQL = ' INSERT into #product select ' + @StrSelect + ', ' + @seedtype +
--' from ' + @StrFrom + @StrWhere
--Exec @strSQL
--Set @strSQL = '
--Set @strSQL = ' SELECT * FROM (select Row_number () over (' + @strOrder + ') as ROWID, '
--Set @strSQL = @strSQL + @StrSelect + ' from[#product] ' + @strWhere
--Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str ((@PageIndex-1) * @PageSize + 1)
--Set @strSQL = @strSQL + ' and ' +str (@PageIndex * @PageSize)
--End
-Else if (@ProductType =3)
--Begin
--Set @strSQL = ' SELECT * FROM (select Row_number () over (' + @strOrder + ') as ROWID, '
--Set @strSQL = @strSQL + @StrSelect + ' from[#product] ' + @StrWhere
--Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str ((@PageIndex-1) * @PageSize + 1)
--Set @strSQL = @strSQL + ' and ' +str (@PageIndex * @PageSize)
--End
-Else if (@ProductType =4)
--Begin
--Set @strSQL = ' SELECT * FROM (select Row_number () over (' + @strOrder + ') as ROWID, '
--Set @strSQL = @strSQL + @StrSelect + ' from[#product] ' + @StrWhere
--Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str ((@PageIndex-1) * @PageSize + 1)
--Set @strSQL = @strSQL + ' and ' +str (@PageIndex * @PageSize)
--End
EXEC (@strSQL)
drop table #product
End