分页存储过程

create proc FY_GetCityList 
(
    @page int,
    @rows int,
    @rowCount int out
)
as 
begin
--定义字符串变量，表示之后执行的sql语句
declare @strSql nvarchar(2000)
set @strSql=N'select top('+STR(@rows)+') * from (select ROW_NUMBER() over(order by id) as rowNum,* from city )as a where rowNum>'+STR((@page-1)*@rows)

print @strSql --打印输出拼接后的sqlyuj
--执行sql语句
exec(@strSql)
--获取总记录数
set @strSql = 'select @total=count(0) from city'
print @strSql

exec sp_executesql @strSql,N'@total int out',@total=@rowCount out

end

--执行存储过程
declare @count int
exec FY_GetCityList 1,3,@count out
print @count