SQL Server paging query common stored procedures (only for paging query), SQL stored procedures

SQL Server paging query common stored procedures (only for paging query), SQL stored procedures

It has been used since the project was started. SORRY, the creator of this stored procedure, wrote this SQL code very well. On this basis, I adjusted the code according to my habits and ways of thinking, only query by page.

/* Author * procedure name: P_PageResult * author: Fay * create date: 2014-07-18 */create procedure prcPageResult -- get data on a page -- @ currPage int = 1, -- current page number (Top currPage) @ showColumn varchar (2000) = '*', -- the expected field (column1, column2 ,......) @ tabName varchar (2000), -- Name of the table to be viewed (from table_name) @ strCondition varchar (2000) = '', -- Query condition (where condition ......) do not add wher E keyword @ ascColumn varchar (100) = '', -- Name of the sorted field (that is, order by column asc/desc) @ bitOrderType bit = 0, -- type of sorting (0 is in ascending order, 1 in descending order) @ pkColumn varchar (50) = '', -- primary key name @ pageSize int = 20 -- page size ASBEGIN -- stored procedure start -- several variables required for the stored procedure -- DECLARE @ strTemp varchar (1000) DECLARE @ strSql varchar (4000) -- DECLARE @ strOrderType varchar (1000) -- sort type Statement (order by column asc or order by column desc) BEGINIF @ bitOrderType = 1 -- BitOrderType = 1: Execute inset @ strOrderType = 'ORDER BY' + @ ascColumn + 'desc' SET @ strTemp = '<(SELECT min 'endelsebeginset @ strOrderType = 'ORDER' + @ ascColumn + 'asc' SET @ strTemp = '> (SELECT max 'endif @ currPage = 1 -- if it is the first BEGINIF @ strCondition! = ''Set @ strSql = 'select top' + STR (@ pageSize) + ''+ @ showColumn + 'from' + @ tabName + 'where' + @ strCondition + @ strOrderTypeELSESET @ strSql = 'select top' + STR (@ pageSize) + ''+ @ showColumn + 'from' + @ tabName + @ strOrderTypeENDELSE -- BEGINIF @ strCondition on other pages! = ''Set @ strSql = 'select top' + STR (@ pageSize) + ''+ @ showColumn + 'from' + @ tabName + 'where' + @ strCondition + 'and' + @ pkColumn + @ strTemp + '(' + @ pkColumn + ') '+' FROM (select top '+ STR (@ currPage-1) * @ pageSize) + ''+ @ pkColumn + 'from' + @ tabName + @ strOrderType + ') AS TabTemp) '+ @ strOrderTypeELSESET @ strSql = 'select top' + STR (@ pageSize) + ''+ @ showColumn + 'from' + @ tabName + 'where' + @ pkColumn + @ strTemp + '(' + @ pkColumn + ') '+' FROM (select top '+ STR (@ currPage-1) * @ pageSize) + ''+ @ pkColumn + 'from' + @ tabName + @ strOrderType + ') AS TabTemp) '+ @ strOrderTypeENDENDEXEC (@ strSql) END -- END of Stored Procedure ---------------------------------------------- GO

Call method:

PrcPageResult 1, '*', 'tablename', '', 'createdate', 1, 'pkid', 25

As shown above, all the fields in the TableName table are queried. The first 25 records are queried. The sorting field is CreateDate on the first page. The primary key is PkID. This stored procedure has powerful functions and is very suitable for projects. If you don't believe it, you can try it, especially in the case of millions of data, its advantages will be revealed. Of course, this code can be converted into a MySql stored procedure. However, here we will not give it to you. You can try to switch it yourself.

The number of records in the following stored procedure query table:

/* ------------------------------------------ * Procedure name: prcRowsCount * author: Fay * create date: 2014-07-18 */create proc prcRowsCount @ tabName varchar (200 ), -- Name of the table to be queried @ colName varchar (200) = '*', -- Name of the column to be queried @ condition varchar (200) = ''-- Query condition ASBEGINDECLARE @ strSql varchar (255) IF @ condition ='' SET @ strSql = 'select count ('+ @ colName + ') from '+ @ tabNameELSESET @ strSql = 'select count (' + @ colName + ') from' + @ tabName + 'where' + @ conditionEXEC (@ strSql) END --------------------------------------- --GO

There is also a general Stored Procedure for deleting records and a general Stored Procedure for querying a single record, which will not be provided here. Thanks to the friends who provided the original General paging query stored procedure, thank you.

How can I use stored procedure query to define a paging stored procedure in sqlserver?

Try:

Private DataTable GetDataByPageProc (String TableName, String Primarykey, String FieldsName, String ByWHERE
, String ByOrder, int PageSize, int PageIndex, ref int RecordCount, ref int PageCount)
{
SqlConnection cn = new SqlConnection (Function. ConnectionString );
String SQL = "exec P_SlipPage @ TableName = @ a, @ Primarykey = @ B, @ FieldsName = @ c, @ ByWHERE = @ d, @ ByOrder = @ e, @ PageSize = @ f, @ PageIndex = @ g, @ RecordCount = @ h, @ PageCount = @ I ";
Using (SqlDataAdapter da = new SqlDataAdapter (SQL, cn ))
{
Da. SelectCommand. Parameters. AddWithValue ("@ a", TableName );
Da. SelectCommand. Parameters. AddWithValue ("@ B", Primarykey );
Da. SelectCommand. Parameters. AddWithValue ("@ c", FieldsName );
Da. SelectCommand. Parameters. AddWithValue ("@ d", ByWHERE );
Da. SelectCommand. Parameters. AddWithValue ("@ e", ByOrder );
Da. SelectCommand. Parameters. AddWithValue ("@ f", PageSize );
Da. SelectCommand. Parameters. AddWithValue ("@ g", PageIndex );
Da. SelectCommand. Parameters. AddWithValue ("@ h", RecordCount );
Da. SelectCommand. Parameters. AddWithValue ("@ I", PageCount );
DataTable dt = new DataTable ();
Da. Fill (dt );
Return dt;
}
}... Remaining full text>
 
In SQL Server, how does the stored procedure with paging query return three tables? The third table is correct. How can we make it return only the third table?

Save the returned data table to DataSet, and then take the third table from DataSet.
DataSet ds = .....
DataTable dt = new DataTable ();
Dt = ds. Tables [2];

Paging can also be used:
With temp
(Select ROW_NUMBER () over (column name of order by sort) as RowIndex, * from table name)
Select * from temp where RowIndex between start row and end row