感觉你写的存储过程比较简单.
效率就不知道了.因为我没有百万级的数据库.
我也写了一个简单的存储过程.

[CODE]IF EXISTS(SELECT * FROM dbo.SYSOBJECTS WHERE ID=OBJECT_ID(N'[GetRecordFromPage]') AND
OBJECTPROPERTY(ID,N'IsProcedure')=1)
DROP PROC dbo.[GetRecordFromPage]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC GetRecordFromPage
@tblName VARCHAR(255), --表名
@strGetFields VARCHAR(1000)='*', --查询的列
@fldName VARCHAR(255)='', --排序的列
@pageSize INT=10, --每页的大小
@pageIndex INT=1, --第几页
@doCount BIT=0, --返回的记录总数,非0则返回
@orderType BIT=0, --排序类型,非0则降序
@strWhere VARCHAR(1500)='' --排序条件
AS
BEGIN
DECLARE @str VARCHAR(5000)
DECLARE @temp VARCHAR(120)
DECLARE @strOrder VARCHAR(400)

IF @doCount!=0
BEGIN
IF @strWhere!=''
SET @str='SELECT COUNT(1) AS Total FROM ['+@tblName+'] WHERE '+@strWhere
ELSE
SET @str='SELECT COUNT(1) AS Total FROM ['+@tblName+']'
END
ELSE
BEGIN
IF @orderType!=0
BEGIN
SET @temp='<=(SELECT MIN'
SET @strOrder=' ORDER BY ['+@fldName+'] DESC'
END
ELSE
BEGIN
SET @temp='>=(SELECT MAX'
SET @strOrder=' ORDER BY ['+@fldName+'] ASC'
END
IF @pageIndex > 1
BEGIN
IF @strWhere=''
SET @str='SELECT TOP '+CAST(@pageSize AS VARCHAR)+' '+@strGetFields+' FROM ['
+@tblName+'] WHERE ['+@fldName+'] '+@temp+'(['
+@fldName+']) FROM (SELECT TOP '+CAST((@pageIndex-1)*@pageSize AS VARCHAR)+' ['
+@fldName+'] FROM ['+@tblName+'] '
+@strOrder+') AS tblTEMP) '+@strOrder
ELSE
SET @str='SELECT TOP '+CAST(@pageSize AS VARCHAR)+' '+@strGetFields+' FROM ['
+@tblName+'] WHERE ['+@fldName+']'+@temp+' (['
+@fldName+']) FROM (SELECT TOP '+CAST((@pageIndex-1)*@pageSize AS VARCHAR)+' ['
+@fldName+'] FROM ['+@tblName+'] WHERE '+@strWhere+' '
+@strOrder+') AS tblTEMP) AND '+@strWhere+' '+@strOrder
END
ELSE
BEGIN
IF @strWhere=''
SET @str='SELECT TOP '+CAST(@pageSize AS VARCHAR)+' '+@strGetFields+' FROM ['
+@tblName+'] '+@strOrder
ELSE
SET @str='SELECT TOP '+CAST(@pageSize AS VARCHAR)+' '+@strGetFields+' FROM ['
+@tblName+'] WHERE '+@strWhere+' '+@strOrder
END
END
--执行SQL
EXEC (@str)
--PRINT @str
END
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO[/CODE]