Stored Procedure Implementation for paging

--- Paging Stored Procedure
Drop table s_info

Create Table s_info
(
S_id nvarchar (20) null,
S_name nvarchar (20) null,
S_sex nchar (1) null
)

Insert into DBO. s_info (s_id, s_name, s_sex)
Values ('1', 'zhang Sanfeng ', 'male ');
Insert into DBO. s_info (s_id, s_name, s_sex)
Values ('2', 'Weekly renewal ruo', 'female ');
Insert into DBO. s_info (s_id, s_name, s_sex)
Values ('3', 'song qingshu ', 'male ');
Insert into DBO. s_info (s_id, s_name, s_sex)
Values ('4', 'wang Li Hong ', 'male ');
Insert into DBO. s_info (s_id, s_name, s_sex)
Values ('5', 'Jay Chou ', 'male ');
Insert into DBO. s_info (s_id, s_name, s_sex)
Values ('6', 'Kingdom, Male ')

Select * From s_info

-- Obtain the number of records
Go
Declare @ temp nvarchar (500)
Declare @ tablename nvarchar (50)
Declare @ count int
Set @ tablename = n's _ INFO'
Set @ temp = n' select @ A = count (*) from '+ @ tablename
Exec sp_executesql @ temp, n' @ A int output', @ count output
Select @ count

-- Pagination proc
If object_id ('page') is not null
Drop proc page
Go
Create proc page
@ Tablename varchar (20 ),
@ Column varchar (20 ),
@ Currentpage Int = 1,
@ Count Int = 10,
@ Countofpage int output
As
Declare @ SQL nvarchar (1000)
Set @ SQL = n'select top '+ Cast (@ count as nvarchar (3 ))
Set @ SQL = @ SQL + N' * from' + @ tablename + 'where' + @ Column
Set @ SQL = @ SQL + N' not in (select top '+ Cast (@ count * (@ CURRENTPAGE-1) as nvarchar (3 ))
Set @ SQL = @ SQL + ''+ @ column + N' from '+ @ tablename + ')'
Exec (@ SQL)
Declare @ sql2 nvarchar (500)
Set @ sql2 = n' select @ A = count (*) from' + @ tablename
Exec sp_executesql @ sql2, n' @ A int output', @ countofpage output
Set @ countofpage = (@ countofpage + @ count-1)/@ count

-- Execute pagination proc
Go
Declare @ countofpages int
Exec Page's _ info','s _ id', 1, 3, @ countofpages output
Select @ countofpages as page number