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 (2 0),--table name @pageindex int=0,--Start Page index, starting from 0 @pagesize varchar = ' Ten ',--default number of pages per page, default per page of 10 records @params varchar (100) = ' * ', --the field displayed in the pagination results, the default display of all fields @id varchar = ' ID ',--data number sort field, by default Id@orderbyid varchar = ' ASC ',--data number sorting method, The default is a positive order @strwhere varchar,--The query condition of the paging data, must begin with where @strwhere2 varchar (p),--the query condition of the paged data, cannot start with where @orderby varchar (+) = ' ID ASC '--data sort method) Asdeclare @Sql varchar (8000), @PageStartNum varchar (+), @PageEndNum varchar (1000), @ SQL2 varchar (+), @Sql3 varchar (1000)--1. Gets 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. Gets 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:

Generated SQL statement:

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

MS SQL Server Universal stored procedure paging