最大的網(wǎng)站源碼資源下載站，
存儲過程 為 sql server 2000版本，請打開sql server 2000 的查詢分析器執(zhí)行下面的sql 語句。
程序用到的存儲過程（僅支持主鍵排序）
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[sys_quicksortpaging]') and objectproperty(id, n'isprocedure') = 1)drop procedure [dbo].[sys_quicksortpaging]goset quoted_identifier off goset ansi_nulls on gocreate procedure sys_quicksortpaging( @table nvarchar(4000), --表名(必須) @primarykeyfield nvarchar(50), --表的主鍵字段 @field nvarchar (4000)='*', --需要返回字段名(必須) @where nvarchar(1000)=null, --where 條件(可選) @groupby nvarchar(1000) = null, --分組 @orderby nvarchar(1000)=null, --排序用到的字段() @pagenumber int = 1, --要返回的頁(第x頁) (默認為第一頁) @pagesize int = 10, --每頁大小(默認為5) @recordcount int output --返回記錄總數(shù))as set nocount on declare @sorttable nvarchar(100) declare @sortname nvarchar(100) declare @strsortcolumn nvarchar(200) declare @operator nvarchar(50) declare @type varchar(100) declare @prec int if @orderby is null or @orderby = '' set @orderby = @primarykeyfield 
/**//* 獲取用于定位的字段*/ if charindex('desc',@orderby)>0 begin set @strsortcolumn = replace(@orderby, 'desc', '') set @operator = '<=' end else begin if charindex('asc', @orderby) = 0 set @strsortcolumn = replace(@orderby, 'asc', '') set @operator = '>=' end if charindex('.', @strsortcolumn) > 0 begin set @sorttable = substring(@strsortcolumn, 0, charindex('.',@strsortcolumn)) set @sortname = substring(@strsortcolumn, charindex('.',@strsortcolumn) + 1, len(@strsortcolumn)) end else begin set @sorttable = @table set @sortname = @strsortcolumn end select @type=t.name, @prec=c.prec from sysobjects o join syscolumns c on o.id=c.id join systypes t on c.xusertype=t.xusertype where o.name = @sorttable and c.name = @sortname if charindex('char', @type) > 0 set @type = @type + '(' + cast(@prec as nvarchar) + ')' declare @strstartrow nvarchar(50) declare @strpagesize nvarchar(50) declare @strwhere nvarchar(1000) declare @strwhereand nvarchar(1000) declare @strgroupby nvarchar(1000) if @pagenumber < 1 set @pagenumber = 1 set @strpagesize = convert (nvarchar(50), @pagesize) set @strstartrow = convert ( nvarchar(50), (@pagenumber - 1)*@pagesize + 1) if @where is not null and @where !='' begin set @strwhere = ' where '+ @where set @strwhereand= ' and ' + @where end else begin set @strwhere = '' set @strwhereand='' end if @groupby is not null and @groupby != '' begin set @strgroupby = ' group by ' + @groupby end else begin set @strgroupby = '' end declare @strsql nvarchar(4000) set @strsql= ' select @recordcount = count (*) from ' + @table + @strwhere + ' ' + @strgroupby exec sp_executesql @strsql,n'@recordcount int output',@recordcount output--計算總頁數(shù) exec ( ' declare @sort ' + @type + ' set rowcount ' + @strstartrow + ' select @sort = ' + @strsortcolumn + ' from ' + @table + @strwhere + ' ' + @strgroupby + ' order by ' + @orderby + ' set rowcount ' + @strpagesize + ' select '+@field+' from ' + @table + ' where ' + )goset quoted_identifier off goset ansi_nulls on go
新聞熱點
疑難解答
圖片精選
