SQL Server paging Stored Procedure collection

SQL Server2000 paging Stored Procedure

1. Create procedure [DBO]. [getrecordwithpage]
2. @ Fieldstype nvarchar (1000), -- Field List (with type), used for field declaration of @ t table variables, such as photoid int, userid int, phototitle nvarchar (50)
3. @ Fieldslist nvarchar (500), -- Field List (without type), used to read the field of the @ t table variable by PAGE, can also be replaced by *, but the performance will decrease, such: photoid, userid, phototitle
4. @ Selectsrting nvarchar (2000), -- read the record's SELECT statement from the @ t table variable
5. @ Resultorderby nvarchar (200), -- Sort the paging result fields, such as: 'photoid ASC 'in ascending order and 'photoid DESC' in descending order. Note: in descending order, DESC must be added to selectsrting and here.
6. @ Pagesize int, -- page size. 0 indicates that all rows are returned.
7. @ Currentpage int, -- current page. The homepage is 1.
8. @ Recordcount int output -- total number of records returned if the value is not 0
9. As
10. Begin
11. Declare @ strsql varchar (4000)
12. Declare @ SQL nvarchar (1000)
13. Set @ strsql = 'Clare @ t table ('+ @ fieldstype + ');'
14. Set @ strsql = @ strsql + 'insert into @ t' + @ selectsrting + ';'
15. Set @ SQL = @ strsql + 'select @ AA = count (*) from @ t ;'
16. Exec sp_executesql @ SQL, n' @ AA int output', @ recordcount output;
17. If @ pagesize = 0
18. Set @ strsql = @ strsql + 'select' + @ fieldslist + 'from @ t ;'
19. Else
20. If @ currentpage = 1
21. Set @ strsql = @ strsql + 'select top ('+ STR (@ pagesize) +') '+ @ fieldslist +' from @ t ;'
22. Else
23. Begin
24. Set @ strsql = @ strsql + 'select top ('+ STR (@ pagesize) + ') '+ @ fieldslist +' from (select top ('+ STR (@ pagesize * @ currentpage) +') '+ @ fieldslist +', row_number () over (order by '+ @ resultorderby + ')'
25. Set @ strsql = @ strsql + 'as rownumber from @ t'
26. Set @ strsql = @ strsql + ') as R where R. rownumber>' + STR (@ pagesize * (@ currentpage-1) + ';'
27. End
28. Exec (@ strsql)
29. End

========================================================== ======================================