一个分页取数据的存储过程

sqlserver里一个用于分页取数据的存储过程，希望大家多多指教

  

CREATE PROCEDURE dbo.Gy_GetPageRecord

@tbName SYSNAME,

@colKey SYSNAME,

@colSelect NVARCHAR(1000),

@countPerPage INT,

@pageIndex INT,

@sortSql NVARCHAR(1000),

@whereSql NVARCHAR(1000)

AS

DECLARE @sql NVARCHAR(4000)

IF LTRIM(RTRIM(@sortSql))<>'' SET @sortSql=' ORDER BY '+@sortSql

IF LTRIM(RTRIM(@whereSql))=''

 SET @whereSql='1=1'

ELSE

 SET @whereSql='('+@whereSql+')'

SET @sql='SELECT TOP '+CAST(@countPerPage AS NVARCHAR(10))

       +' '+@colSelect+' FROM '+@tbName+' WHERE '+@colKey+' NOT IN

  (SELECT TOP '+CAST(@countPerPage*(@pageIndex-1) AS NVARCHAR(10))

  +' '+@colKey+' FROM '+@tbName+' WHERE '+@whereSql+@sortSql

  +') AND '+@whereSql+@sortSql

EXEC(@sql)

SET @sql='SELECT CEILING(CAST(COUNT(*) AS DECIMAL(18,10))/CAST('

       +CAST(@countPerPage AS NVARCHAR(10))+' AS DECIMAL(18,10))) FROM '

       +@tbName+' WHERE '+@whereSql

EXEC(@sql)

SET @sql='SELECT COUNT(*) FROM '+@tbName+' WHERE '+@whereSql

EXEC(@sql)

GO