Shulou(Shulou.com)06/01 Report--

This article is about how to achieve paging query in SQLsever stored procedures, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Use stored procedures to implement paging queries. The SQL statement is as follows:

USE [DatebaseName]-- name of the database GO/* Object: StoredProcedure [dbo]. [Pagination] Script Date: 03Unitable2019 10:36:52 * / SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCreate PROCEDURE [dbo]. [Pagination] (@ SqlTable varchar (1000),-- A sql statement @ SqlPK varchar (50) for a table or view to query,-- primary key @ SqlField varchar (1000) -- query fields @ SqlWhere varchar (1000) ='',-- query criteria @ SqlOrder varchar (1000),-- number of records per page sorted @ PageSize int=20,-- @ PageIndex int=1,-- Page, default first page @ IsCount bit -- whether to get the number of records @ RecordCount int=0 output) ASSET NOCOUNT ONDECLARE @ PageLowerBound intDECLARE @ PageUpperBound intDECLARE @ sqlstr nvarchar (2000)-- get the number of records IF @ IsCount=1BEGIN SET @ sqlstr=N'select @ sCount=count (1) FROM'+ @ SqlTable+' WHERE 1records 1'+ @ SqlWhere Exec sp_executesql @ sqlstr,N'@sCount int outPut',@RecordCount OUTPUTENDSET @ PageLowerBound= (@ PageIndex-1) * @ PageSizeSET @ PageUpperBound=@PageLowerBound+@PageSizeCREATE TABLE # pageindex (id int identity (1Mague 1) not null Nid varchar) SET rowcount @ PageUpperBound SET @ sqlstr=N'insert into # pageindex (nid) select'+ @ SqlPK+' from'+ @ SqlTable+' where 1'+ @ SqlWhere+''+ @ SqlOrderExec sp_executesql @ sqlstrSET @ sqlstr='select'+ @ SqlField+' FROM'+ @ SqlTable+' inner join # pageindex p on'+ @ SqlPK+'=p.nid and (p.id >'+ STR (@ PageLowerBound) +') and (p.id'+STR (@ PageLowerBound) +') and (p.id)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.