分页(存储过程)

USE [Geography]
GO
/****** Object: StoredProcedure [dbo].[Sp_Paging] Script Date: 03/06/2014 14:43:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Sp_Paging]
(
@TbName nvarchar(50),
@StartIndex int,
@EndIndex int,
@Id nvarchar(50),
@counts int out
)

as
begin

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

SET @SQLString = N'SELECT @IntVariable = Count(*) FROM #tableName#'
set @SQLString=replace(@SQLString,'#tableName#',@TbName)
SET @ParmDefinition = N'@IntVariable INT out'

EXECUTE sp_executesql @SQLString,@ParmDefinition,@counts OUT

declare @Sql nvarchar(max);
set @Sql='select * from (
select *,ROW_NUMBER() OVER (ORDER BY #id#) as rank from #tableName#
) as t where t.rank between #StartIndex# and #EndIndex#'

set @Sql=REPLACE(@Sql,'#StartIndex#',@StartIndex)
set @Sql=replace(@Sql,'#EndIndex#',@EndIndex)
set @Sql=replace(@Sql,'#tableName#',@TbName)
set @Sql=replace(@Sql,'#id#',@Id)
exec(@Sql)


end