CREATE PROCEDURE GetData @tblName varchar(255), -- 表名 @strGetFields varchar(1000) ='*', -- 需要返回的列名 @strWhere varchar(1500) ='', -- 查询条件 (注意: 不要加 where) @fldName varchar(255)='', -- 排序的字段名 @orderType bit =0, -- 设置排序类型, 0 值为升序, 非 0 值为降序 @pageSize int=10, -- 页尺寸 @pageIndex int=1, -- 页码 @returnType bit =0-- 设置返回类型,0 值为返回记录集, 非 0 值为返回记录总数 AS declare @strSQL varchar(5000) -- 主语句 declare @strTmp varchar(110) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 if @returnType !=0-- 返回记录总数 begin if @strWhere !='' set @strSQL ='select count(*) as total from ['+ @tblName +'] where '+ @strWhere else set @strSQL ='select count(*) as total from ['+ @tblName +']' end else-- 返回记录集 begin 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--以下代码赋予了@strSQL以真正执行的SQL代码 begin 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 else 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 end end exec(@strSQL) GO