My general paging
(By Dragonimp)
I have been thinking about it for a long time.
Incorporate various methods written your own paging, ready to call in other processes.
CREATE PROCEDURE SelectPagedSQL (@SQL nvarchar (512), @IndexField nvarchar (100), @PageSize int = 10, @PageIndex int = 1, @Sort nvarchar (128) = @ IndexField, @TotalCount int = 0 output) ASdeclare @strSQL NVARCHAR (1024)
Set @ strsql = 'SELECT * FROM (SELECT TOP' STR (@PageSize) '* from (SELECT TOP' STR (@ PageSize * @ PageIndex) '* from (' @ SQL ') as T0 Order By ' @ Indexfield ' ASC) AS T1 ORDER BY ' @ indexfield ' dec) AS T2 ORDER BY ' @ Sort ' Set NoCount on Select @ Totalcount = Count (*) from (' @ SQL ') AS T0 '
EXEC SP_EXECUTESQL @strsql, n '@ Totalcount Int = 0 Output', @ Totalcount = @ Totalcount OutputGO
After testing, I found a problem
When you write this, you will not use ID> max (id) or directly top n, don't know which faster.
It is now found that the direct TOP is not working, because so if the page is outside the scope of the table, it will still be recorded. That is to say, this is impossible to have no records. So, it is still to change to MAX.
Others have written a max, but his input parameters are not good, you want to specify Tablename, I can specify SQL. I am slowly at this speed, wait for me to see if you see if it is Top.
