CREATEPROCEDURE Pagger @tblNameVARCHAR(255), -- 表名 @strGetFieldsVARCHAR(1000) ='*', -- 需要返回的列 @fldNameVARCHAR(255)='', -- 排序的字段名 @PageSizeINT=10, -- 页尺寸 @PageIndexINT=1, -- 页码 @doCountBIT=0, -- 返回记录总数, 非 0 值则返回 @OrderTypeBIT=0, -- 设置排序类型, 非 0 值则降序 @strWhereVARCHAR(1500) =''-- 查询条件 (注意: 不要加 where) AS DECLARE@strSQLVARCHAR(5000) -- 主语句 DECLARE@strTmpVARCHAR(110) -- 临时变量 DECLARE@strOrderVARCHAR(400) -- 排序类型 IF@doCount!=0 BEGIN -- --以下代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况 -- IF@strwhere!='' SET@strsql='SELECT COUNT(*) AS Total FROM ['+@tblname+'] WHERE '+@strwhere ELSE SET@strsql='SELECT COUNT(*) AS Total FROM ['+@tblname+']' END ELSE BEGIN -- --如果@OrderType不是0,就执行降序,这句很重要! -- IF@OrderType!=0 BEGIN SET@strTmp='<(SELECT MIN' SET@strOrder=' ORDER BY ['+@fldName+'] DESC' END ELSE BEGIN SET@strTmp='>(SELECT MAX' SET@strOrder=' ORDER BY ['+@fldName+'] ASC' END IF@PageIndex=1 BEGIN -- --如果是第一页就执行以上代码,这样会加快执行速度 -- IF@strWhere!='' SET@strSQL='SELECT TOP '+str(@PageSize) +''+@strGetFields+' from ['+@tblName+'] where '+@strWhere+''+@strOrder ELSE SET@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from ['+@tblName+'] '+@strOrder END ELSE BEGIN -- --以下代码赋予了@strSQL以真正执行的SQL代码 -- SET@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from [' +@tblName+'] where ['+@fldName+']'+@strTmp+'(['+@fldName+']) from (select top '+str((@PageIndex-1)*@PageSize) +' ['+@fldName+'] from ['+@tblName+']'+@strOrder+') as tblTmp)'+@strOrder IF@strWhere!='' SET@strSQL='select top '+str(@PageSize) +''+@strGetFields+' from [' +@tblName+'] where ['+@fldName+']'+@strTmp+'([' +@fldName+']) from (select top '+str((@PageIndex-1)*@PageSize) +' [' +@fldName+'] from ['+@tblName+'] where '+@strWhere+'' +@strOrder+') as tblTmp) and '+@strWhere+''+@strOrder END END EXEC (@strSQL) GO