SQL Server Common stored procedure paging code

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