SQL2000通用分页存储过程&gt,把数据总数也返回

  • 栏目:数据 时间:2020-05-03 15:52
<返回列表

mssql 存储过程分页

转:

cs页面调用代码: 复制代码 代码如下: public int TotalPage = 0; public int PageCurrent = 1; public int PageSize = 25; public int RowsCount = 0; string userid, username; public DataTable dt = new DataTable(); public string path, userwelcome; public string opt,cid; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Request.Params["page"] == null || Request.Params["page"].ToString().Equals("")) PageCurrent = 1; else PageCurrent=int.Parse(Request.Params["page"].ToString()); this.getPage(out TotalPage, out RowsCount, PageSize, PageCurrent); } } //调用存储过程的函数 private void getPage(out int totalPage, out int rowsCount, int pageSize, int currentPage) { SqlParameter[] parameters = { new SqlParameter("@TotalPage", SqlDbType.Int,4), new SqlParameter("@RowsCount", SqlDbType.Int,4), new SqlParameter("@PageSize", SqlDbType.Int,4), new SqlParameter("@CurrentPage", SqlDbType.Int,4), new SqlParameter("@SelectFields", SqlDbType.NVarChar,700), new SqlParameter("@IdField",SqlDbType.NVarChar,50), new SqlParameter("@OrderField", SqlDbType.NVarChar,200), new SqlParameter("@OrderType", SqlDbType.NVarChar,2), new SqlParameter("@TableName", SqlDbType.NVarChar,300), new SqlParameter("@strWhere", SqlDbType.NVarChar,300), }; parameters[0].Direction = ParameterDirection.Output; parameters[1].Direction = ParameterDirection.Output; parameters[2].Value = pageSize; parameters[3].Value = currentPage; parameters[4].Value = "a.RLId,a.companyName,a.webSite,a.isRL,a.ordernum,a.isrl,a.userid"; parameters[5].Value = "a.RLId"; parameters[6].Value = " a.isrl asc , a.orderNum "; parameters[7].Value = "1"; parameters[8].Value = "qiYeRenling a"; parameters[9].Value = "1=1";// DataSet ds = Wm23Abc.DBUtility.DbHelperSQL.RunProcedure("getRecordByPage", parameters, "dt"); dt = ds.Tables[0]; totalPage = int.Parse(parameters[0].Value.ToString()); rowsCount = int.Parse(parameters[1].Value.ToString()); } .aspx页面代码: table trtdb公司名称/b/tdtdb公司网址/b/tdtdb认领状态/b/td/tr %for (int i = 0; i dt.Rows.Count; i++) { % tr td%= dt.Rows[i]["companyName"].ToString() %排序值:%= dt.Rows[i]["ordernum"].ToString() %/td td%= dt.Rows[i]["webSite"].ToString() % 是否认领:%=dt.Rows [i]["userid"].ToString () %/td td%= dt.Rows[i]["isRL"].ToString().Equals("0") ? "a href="javascript:;" onclick="renLing(event,'"+dt.Rows[i]["RLId"].ToString()+"');"认领该企业/a" : "font color="red"该企业已被认领/font"%/td /tr % } % /table /div div 第 %=PageCurrent % 页 共 %=RowsCount % 条 共 %=TotalPage% 页 % if (PageCurrent != 1) { % a href="test.aspx"首 页/a a href="test.aspx?page=%=PageCurrent-1 %"/a % } if (PageCurrent != TotalPage) { % a href="test.aspx?page=%=PageCurrent+1 %"/a a href="test.aspx?page=%=TotalPage%"末 页/a % } % /div 存储过程代码: 复制代码 代码如下: CREATE proc [dbo].[getRecordByPage] @TotalPage int output,--总页数 @RowsCount int output,--总条数 @PageSize int,--每页多少数据 @CurrentPage int,--当前页数 @SelectFields nvarchar(1000),--select 语句但是不包含select @IdField nvarchar(50),--主键列 @OrderField nvarchar(50),--排序字段,如果是多个字段,除最后一个字段外,后面都要加排序条件,不包含order by,最后一个排序字段不用加排序条件 @OrderType nvarchar(4),--1升序,0降序 @TableName nvarchar(200),--表名 @strWhere nvarchar(300)--条件 As Begin declare @RecordCount float declare @PageNum int --分页依据数 Declare @Compare nvarchar(50)--比较字段区分min或者max Declare @Compare1 nvarchar(2) --大于号“” 或者小于号"“ Declare @OrderSql nvarchar(10)--排序字段 declare @Sql nvarchar(4000) Declare @TemSql nvarchar(1000) Declare @nRd int declare @afterRows int declare @tempTableName nvarchar(10) if(@OrderType='1') Begin set @OrderSql=' asc' End Else Begin set @OrderSql= ' desc' End if(isnull(@strWhere, '')'') Set @strWhere = @strWhere if(@strWhere='') Set @strWhere=' 1=1 ' Set @TemSql='Select @RecordCount=Count(1) from '+@TableName +' where '+@strWhere exec sp_executesql @TemSql,N'@RecordCount float output',@RecordCount output Set @RowsCount=@RecordCount Set @TotalPage= ceiling(@RecordCount/@PageSize) if(@CurrentPage@TotalPage) Set @CurrentPage=@TotalPage if(@CurrentPage1) Set @CurrentPage=1 if(@PageSize1) Set @PageSize=1 print(@RecordCount) if(@CurrentPage=1) Begin set Rowcount @PageSize set @Sql='select '+ @SelectFields +' from '+ @TableName +' where ' +@strWhere+' order by '+@OrderField +' '+@OrderSql +','+@IdField +' asc' --print(@Sql) exec sp_executeSql @Sql End else if(@CurrentPage=@TotalPage) begin set @afterRows=@RowsCount-(@CurrentPage-1)*@PageSize set RowCount @afterRows if(@OrderType='1') begin set @OrderField=REPLACE(@OrderField,'asc','lai512343975')//这里用变量将asc和desc互换,哈哈,太神了 set @OrderField=REPLACE(@OrderField,'desc','asc') set @OrderField=REPLACE(@OrderField,'lai512343975','desc') set @Sql='select ' + @SelectFields +' from '+ @TableName +' where ' +@strWhere+' order by '+@OrderField +' desc'+','+@IdField +' asc' end else begin set @OrderField=REPLACE(@OrderField,'desc','lai512343975') set @OrderField=REPLACE(@OrderField,'asc','desc') set @OrderField=REPLACE(@OrderField,'lai512343975','asc') set @Sql='select '

之前都是获取分页数据、获取数据总数分开写,也就是做两次查询。

-- =============================================
-- Author:        <jiangrod>
-- Create date: <2010-03-03>
-- Description:    <SQL2000通用分页存储过程>调用方法: sp_Pager2000 'xtest','*','ID','ORDER BY ID ASC','xname like ''%222name%''',3,20,0,0
-- =============================================
ALTER PROCEDURE [dbo].[sp_Pager2000]
@tblName   varchar(255),            -- 表名如:'xtest'
@strGetFields varchar(1000) = '*',  -- 需要返回的列如:'xname,xdemo'
@pkName        nvarchar(50)='ID',        -- 主键名
@strOrder varchar(255)='',          -- 排序的字段名如:'order by id desc'
@strWhere  varchar(1500) = '',      -- 查询条件(注意:不要加where)如:'xname like ''%222name%''' 
@pageIndex  int = 1,                -- 页码如:2
@pageSize   int = 20,               -- 每页记录数如:20
@recordCount int=0 out,             -- 记录总数
@doCount bit=0                        -- 非0则统计,为0则不统计(统计会影响效率)
AS

某天突然想了想,干脆把这两步操作都写到分页查询里,返回分页数据的同时,把数据总数也返回。

Declare @sql         nvarchar(4000)
Declare    @sqltemp    nvarchar(1000)
set @sql=' From '+@tblName
if(@strWhere!='') set @sql=@sql+' Where '+@strWhere

这么做有什么好处呢?

declare @strCount nvarchar(1000)
--总记录条数
if(@doCount!=0)
begin
    if(@strWhere !='')
    begin
        set @strCount='set @num=(select count(1) from '+ @tblName + ' where '+@strWhere+' )'
    end
    else
    begin
        set @strCount='set @num=(select count(1) from '+ @tblName + ' )'
    end
    EXECUTE sp_executesql @strCount ,N'@num INT output',@RecordCount output
end

1、减少一次数据库教程请求。

if @strWhere !=''
begin
    set @strWhere=' where '+@strWhere
end

2、查询条件很多时,就只需拼一次SQL了。

if (@PageIndex>1)
begin
    set @sqltemp=@PKName+' not in (Select Top '+cast((@PageSize*(@PageIndex-1)) as nvarchar)+' '+@PKName+' '+@sql
    if(@strOrder!='')set @sqltemp=@sqltemp+' '+@strOrder
    set @sqltemp=@sqltemp+')'
    set @sql='Select Top '+cast(@PageSize as nvarchar)+' '+@strGetFields+' '+@sql 
    if(@strWhere!='')set @sql=@sql+' And '+@sqltemp
    else set @sql=@sql+' Where '+@sqltemp
end
else
begin
    set @sql='Select Top '+cast(@PageSize as nvarchar)+' '+@strGetFields+' '+@sql 
end
if(@strOrder!='') set @sql=@sql+' '+@strOrder
exec(@sql)

3、方便维护

-- =============================================
-- Author:        <jiangrod>
-- Create date: <2010-03-03>
-- Description:    <SQL2005及后续版本通用分页存储过程>调用方法: sp_Pager2005 'xtest','*','ORDER BY ID ASC','xname like ''%222name%''',2,20,0,0
-- =============================================
ALTER PROCEDURE [dbo].[sp_Pager2005]
@tblName   varchar(255),            -- 表名如:'xtest'
@strGetFields varchar(1000) = '*',  -- 需要返回的列如:'xname,xdemo'
@strOrder varchar(255)='',          -- 排序的字段名如:'order by id desc'
@strWhere  varchar(1500) = '',      -- 查询条件(注意:不要加where)如:'xname like ''%222name%''' 
@pageIndex  int = 1,                -- 页码如:2
@pageSize   int = 20,               -- 每页记录数如:20
@recordCount int output,            -- 记录总数
@doCount bit=0                        -- 非0则统计,为0则不统计(统计会影响效率)
AS

4、。。。。。。

declare @strSQL varchar(5000)
declare @strCount nvarchar(1000)
--总记录条数
if(@doCount!=0)
begin
    if(@strWhere !='')
    begin
        set @strCount='set @num=(select count(1) from '+ @tblName + ' where '+@strWhere+' )'
    end
    else
    begin
        set @strCount='set @num=(select count(1) from '+ @tblName + ' )'
    end
    EXECUTE sp_executesql @strCount ,N'@num INT output',@RecordCount output
end

这几个理由,足够了,那么就实践吧。

if @strWhere !=''
begin
    set @strWhere=' where '+@strWhere
end
set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
set @strSQL=@strSQL+@strGetFields+' FROM ['+@tblName+'] '+@strWhere
set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
exec (@strSQL)

-- =============================================-- Author: Henson-- Create date: 2011-06-01-- Description: 根据查询条件返回符合的条件的数据总数及当页数据-- =============================================CREATE PROCEDURE [dbo].[ST_GetPagedData] @MbCd varchar(12),--会员编号 @MbName varchar(20),--会员姓名 @PageSize int,--每页显示的记录数 @CurrPage int, --当前页索引 @RecordCount int output --总记录数(输出参数)ASBEGIN Declare @strSql varchar(4000) Declare @strWhere varchar(1000) --根据参数生成Where条件 Set @strWhere = '' If (isnull(@MbCd,'') != '') Begin Set @strWhere = @strWhere + ' And MB_CD='''+@MbCd+'''' End If (isnull(@MbName,'') != '') Begin Set @strWhere = @strWhere + ' And Name like ''%'+@MbName+'%''' End --获取记录总数 Declare @strCountSql nvarchar(1000) Set @strCountSql = 'Select @RecordCount=count(*) From M_Account a Where 1=1 '+@strWhere exec sp_executesql @strCountSql, N'@RecordCount int output',@RecordCount output --print (@strCountSql) --获取数据 declare @topIdNum int ,@delNum int set @topIdNum = @PageSize*@CurrPage set @delNum = (@CurrPage-1)*@PageSize set @strSql = ' DECLARE @temptb table ( uid int identity(1,1), Mb_Cd varchar(12) ) Insert Into @temptb(Mb_Cd) Select Top '+ cast( @topIdNum as varchar(10) )+' a.Mb_Cd, From M_韦德体育 ,Account a Where 1=1 '+@strWhere+N'

delete from @temptb where uid='+ cast( @delNum as varchar(10) )+N'

Select * From M_Account a Inner Join @temptb b On a.Mb_Cd=b.Mb_Cd ' exec (@strSql) END

嗯,就是这样。

然后在调用时,将输出参数的值,也一并获取,就OK了

上一篇:数据类型实现有需要的朋友可以参考一下 下一篇:没有了

更多阅读

SQL2000通用分页存储过程&gt,把数据总

数据 2020-05-03
mssql 存储过程分页 转: cs页面调用代码: 复制代码 代码如下:public int TotalPage = 0; public int Pa...
查看全文

数据类型实现有需要的朋友可以参考一下

数据 2020-05-03
一个sql uniqueidentifier转换成varchar数据类型实现有需要的朋友可以参考一下 niqueidentifier 数据类型...
查看全文

代码如下复制代码 select a.

数据 2020-05-02
本文章提供了大量的sql删除重复记录语句,如果你正在愁如何把数据库重复的数据给删除的话...
查看全文

友情链接: 网站地图

Copyright © 2015-2019 http://www.koi-bumi.com. 韦德体育有限公司 版权所有