"Old Code Farm Nostalgia" A simple and easy-to-use paging stored procedure

A database stored procedure is a set of SQL statements that are pre-created and stored on the database server with the specified name, and will be used in a more frequent or complex operation, pre-written with SQL statements and stored with a specified name, and later when a database is required to provide the same functionality as a defined stored procedure. Simply execute the stored procedure again.

Advantages of database stored procedures:
(1) The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can greatly improve the database execution speed.
(2) Typically, complex business logic requires multiple SQL statements. These statements are sent separately from the client to the server, and when there are many operations between the client and server, a large amount of network traffic is generated. If these operations are placed in a stored procedure, the network transport between the client and the server is greatly reduced and the network load is reduced.
(3) A stored procedure can be reused once it is created, reducing the workload of the database developer.
(4) High security, stored procedures can block direct access to the underlying database objects, using EXECUTE permissions to call the stored procedure, without having access to the underlying database object explicit permissions.

Disadvantages of database stored procedures:
(1) The limitations of SQL itself-sql is a language oriented to structured query, which is essentially process-oriented, and can be overwhelmed by complex business logic.
(2) Poor transplant, different database SQL syntax some differences
(3) Difficult to debug
(4) The developer of SQL capacity requirements are relatively high. PS: Now many novices don't know how to create a stored procedure ╮ (╯_╰) ╭
Currently common database support stored procedures, such as IBM Db2,microsoft SQL server,oracle,access, and so on, open source database system MySQL is also in 5.0 to achieve the storage process support.

A simple and easy-to-use paging stored procedure implementation

CREATE PROCEDURE [dbo]. [Sp_k_page] (@totalCount INT OUTPUT,--the total number of records filtered @tablename varchar (max),--table name @ordersql varchar (max),--sort field @where nvarchar (max) = ', --Filter conditions @pageindex int=1,--page @pagesize int=10--page size) as DECLARE @pageSql NVARCHAR (MAX); --Paging Sqldeclare @whereSql NVARCHAR (MAX); --Filter Sqldeclare @coungtSql NVARCHAR (MAX);--Total record count sqlset @pageIndex = '; SET @whereSql = '; SET @totalCount = '; Set @coungtSql = ', IF @where <> ' BEGIN SET @whereSql = ' where (' + @where + ') '; ENDIF @pageIndex <=0beginset @pageIndex = 1; END IF @pageSize <=0begin SET @pageSize = 10; END SET @coungtSql = ' SELECT COUNT (*) from ' + @tableName + ' WHERE ' [email protected];exec sp_executesql @coungtSql, N ' @ Count INT OUTPUT ', @totalCount outputset @pageSql = ' SELECT * FROM (select Row_number () over (ORDER by ' + @orderSql + ') PA  Geindex, * from ' + @tableName [email protected]+ ') t_page WHERE pageIndex > ' + CONVERT (VARCHAR, @pageIndex) + ' and PageIndex <= ' +convert (VARCHAR, @pageSize); EXEC (@pageSql) godeclare @Count INT; ExeC sp_k_page @Count OUTPUT, ' COURSE ', ' cource_name ', ' Createtime < GETDATE ' 

I miss the running of the sunset, that is my lost green onion! End.

"Old Code Farm Nostalgia" A simple and easy-to-use paging stored procedure