SQL Server Paging query Common stored procedure (only for paging queries) _mssql

The

has been used since the start of the project. The original of this stored procedure (SORRY, forget the name), write this section of the SQL code is very good, I on this basis, according to my habits and thinking mode, adjusted the code, only to do paging query.

/*----------------------------------------------*procedure name:p_pageresult * Author:fay * Create date:2014-07-18 * * CREATE PROCEDURE Prcpageresult--Get data for a page--@currPage int = 1,--current page number (that is, top currpage) @showColumn varchar (2000) = ' *  ',--the desired field (i.e. Column1,column2,......) @tabName varchar (2000)--The table name (that is, the from table_name) that needs to be viewed @strCondition varchar (2000) = ',--query criteria (that is, where condition ...) do not need to add the WHERE keyword @ascColumn varchar (100) = ',--the sorted field name (that is, order by column Asc/desc) @b
Itordertype bit = 0,--sort type (0 is ascending, 1 is descending) @pkColumn varchar (50) = ',--primary key name @pageSize int = 20-Paging size as Begin-stored procedure starts --Several variables to be used for the stored procedure--DECLARE @strTemp varchar (1000) DECLARE @strSql varchar (4000)--The last statement executed by the stored procedure DECLARE @strOrderType var CHAR (1000)--Sort Type statement (order BY column ASC or ORDER BY column desc) BEGIN IF @bitOrderType = 1-bitordertype=1 to perform descending begin S ET @strOrderType = ' ORDER BY ' + @ascColumn + ' DESC ' Set @strTemp = ' < (SELECT min ' End ELSE BEGIN SET @strOrderType = ' O Rder by ' + @ascColumn + ' asC ' Set @strTemp = ' > (select Max ' End If @currPage = 1--If it is the first page BEGIN if @strCondition!= ' Set @strSql = ' Select top ' +str (@pageSize) + ' + @showColumn + ' from ' + @tabName + ' WHERE ' + @strCondition + @strOrderType ELSE SET @strSql = ' SELECT top ' +str (@pageSize) + ' + @showColumn + ' from ' + @tabName + @strOrderType End Else--other pages BEGIN IF @strCondition!= ' 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) ' + @strOrderType ELSE SET @strSql = ' SELECT top ' +str (@pageSize) + ' + @showColumn + ' from ' + @tabName + ' WHERE ' + @pkCo lumn+ @strTemp + ' (' + @pkColumn + ') ' + ' from (SELECT top ' +str (@currPage-1) * @pageSize) + "+ @pkColumn + ' from ' + @tabName +@ Strordertype+ ') as Tabtemp) ' + @strOrderType end EXEC (@strSql) ending--the stored procedure ends---------------------------------------- --------Go

Call Method:

Prcpageresult 1, ' * ', ' tablename ', ', ' createdate ', 1, ' Pkid ', 25

Above, the query table tablename all the fields, the first 25 records, because it is the first page, the Sort field is createdate in descending order, and the primary key is Pkid. This stored procedure is quite powerful and is very useful in the project. Do not believe you can try, especially in the millions data, his advantages will be revealed, of course, this code can be converted into MySQL in the stored procedures, but here is not for everyone, you can try to change their own look.

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

/*----------------------------------------------
*procedure name:prcrowscount
* Author:fay
* Create date:2014-07-18
/
CREATE PROC prcrowscount
@tabName varchar (200)--the table name that needs to be queried
@colName varchar (200) = ' * ',--the column name that needs to be queried
@condition varchar (200) = '--Query condition as
BEGIN
DECLARE @strSql varchar (255)
IF @ Condition = '
set @strSql = ' Select count (' + @colName + ') from ' + @tabName
ELSE
SET @strSql = ' Select count (' + @colName + ') from ' + @tabName + ' where ' + @condition
EXEC (@strSql)
end
------------------------------- -----------------Go

There are common stored procedures for deleting records and a common stored procedure for querying a single record here is not to be given away, thank you for providing the original general paging query stored procedures buddy, thank you.