MS SQL Server Universal stored procedure paging

Recently, in the interview, met a wonderful bald old man interview officer.

Q: Have you ever written a stored procedure paging?

A: not written, but I know the principle of paging storage, I wrote it myself, just did not write at work.

Q: So you haven't written in your work for so many years?

A: I did not write it, because the amount of project data is very small.

Then, the wonderful flower will get up and leave, not even a greeting. Think I interviewed so many companies, this wonderful job interview officer, or the first to see, a little professional quality, polite all wood has. It's killing me!

Tomorrow to go into the new company, today, Idle to do nothing, try to write this stored procedure paging. Only tested on SQLSERVER2008, but I think the above version of 2000 should be supported.

Nonsense to this, on the code.

if  (object_id (' proc_paging ',  ' P ')  is not null)     drop proc  proc_paginggocreate procedure proc_paging (@TableName  varchar (,--) Table name @pageindex int= 0,--Start Page index, starting from 0 @pagesize varchar (+) = ' Ten ',--the default number of pages per page, default per page 10 records @params varchar (+) = ' * ',-- The fields displayed in the results of the page are displayed by default, all fields @id varchar = ' ID ',--the data number sort field, by default Id@orderbyid varchar (P) = ' ASC ',--data number sorting method, By default, the query criteria for the @strwhere varchar,--paging data must begin with where @strwhere2 varchar,--the query criteria for the paged data. Cannot start with where @orderby varchar = ' id asc '--data sort method) asdeclare  @Sql  varchar (8000), @ Pagestartnum varchar (+), @PageEndNum  varchar (+), @Sql2  varchar (+), @Sql3  varchar ( 1000)--1. Get the specified page data set  @PageStartNum = (@PageIndex) * (@PageSize)--Start data number set  @PageEndNum = (@PageIndex + 1) * (@ PageSize)--cutoff data number set  @Sql = ' Select row_number ()  over (order by  ' [email protected]+ '   ' [email protected]+ ')  num, ' [email protected]+ '   ' + ' into  #temp   ' + ' from  ' [email protected]+ '   ' [email  protected]+ ' + ' select * from  #temp  where Num between  ' + ' + (@PageStartNum) + '  and  ' + (@PageEndNum) + '   ' [email protected]+ '  order by  ' [email protected ]+ '   ' EXEC (@Sql)--print  @Sql--2. Get the total number of records set  @Sql2 = ' Select count (1)  from  ' [email  protected]+ ' EXEC (@Sql2)--print  @Sql2--3. Gets the total number of pages set  @Sql3 = ' select ceiling (count (1) *1.0/(' [ email protected]+ ')  from  ' [email protected]+ ' EXEC (@Sql3)--print  @Sql3go--Test code exec  Proc_Paging  ' Customers ', 2,10, ' Customerid,companyname,address,city,postalcode ', ' CustomerID ', ' ASC ', ' Where postalcode> '   ', ' and num>1  ', ' NUM ASC ' Go

The following is the result of the operation.

Execution Result:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/82/wKioL1UvfJPAQF7DAAKy9EZv-io744.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1uvfjpaqf7daaky9ezv-io744.jpg "/>

Generated SQL statement:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/86/wKiom1Uvez-jaRYRAAF0uZ7sZ3c674.jpg "style=" float: none; "title=" 2.png "alt=" Wkiom1uvez-jaryraaf0uz7sz3c674.jpg "/>

Big God, don't squirt, rookie, Pat. Thank you!

This article is from the "Swaying Kite" blog, please be sure to keep this source http://pinzi.blog.51cto.com/4845903/1633514

MS SQL Server Universal stored procedure paging