存储过程.txt

sql server分页存储过程 ASP.net 控件,网上着的

 CREATE PROCEDURE  GetRecordFromPage   
          @tblName            varchar(255),                --   表名 
          @RetColumns         varchar(1000) = '*',         --   需要返回的列，默认为全部   
          @Orderfld           varchar(255),                --   排序字段名   
          @PageSize           int  =  10,                  --   页尺寸   
          @PageIndex          int  =  1,                   --   页码   
          @IsCount            bit  =  0,                   --   返回记录总数,   非   0   值则返回   
          @OrderType          varchar(50)  =  'asc',       --   设置排序类型,   非   asc   值则降序   
          @strWhere           varchar(1000)  =  ''         --   查询条件   (注意:   不要加   where)   
  AS
      
  declare   @strSQL       varchar(1000)          --   主语句   
  declare   @strTmp       varchar(300)           --   临时变量   
  declare   @strOrder     varchar(400)           --   排序类型   
    
  if  @IsCount  !=   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  !=   'asc'   
		begin   
				set   @strTmp   =   "<(select   min"   
				set   @strOrder   =   "   order   by   ["   +   @Orderfld   +"]   desc"   
		end   
	else   
		begin   
				set  @strTmp   =   ">(select   max"   
				set  @strOrder   =   "   order   by   ["   +   @Orderfld   +"]   asc"   
		end   
		
	set  @strSQL   =   "select   top   "   +   str(@PageSize)   +   "   " + @RetColumns + "    from   ["   
			+   @tblName   +   "]   where   ["   +   @Orderfld   +   "]"   +   @strTmp   +   "(["   
			+   @Orderfld   +   "])   from   (select   top   "   +   str((@PageIndex-1)*@PageSize)   +   "   ["   
			+   @Orderfld   +   "]   from   ["   +   @tblName   +   "]"   +   @strOrder   +   ")   as   tblTmp)"   
			+   @strOrder   
		
	if  @strWhere  !=   ''   
			set   @strSQL   =   "select   top   "   +   str(@PageSize)   +   "   " + @RetColumns + "   from   ["   
					+   @tblName   +   "]   where   ["   +   @Orderfld   +   "]"   +   @strTmp   +   "(["   
					+   @Orderfld   +   "])   from   (select   top   "   +   str((@PageIndex-1)*@PageSize)   +   "   ["   
					+   @Orderfld   +   "]   from   ["   +   @tblName   +   "]   where   ("   +   @strWhere   +   ")   "   
					+   @strOrder   +   ")   as   tblTmp)   and   ("   +   @strWhere   +   ")   "   +   @strOrder   
		
	if  @PageIndex   =   1   
		begin   
				set   @strTmp   =   ""   
				if   @strWhere   !=   ''   
						set   @strTmp   =   "   where   ("   +   @strWhere   +   ")"   
			
				set   @strSQL   =   "select   top   "   +   str(@PageSize)   +   "   " + @RetColumns + "   from   ["  		
						+   @tblName   +   "]"   +   @strTmp   +   "   "   +   @strOrder   
		end
 end
    
exec  (@strSQL)