8,497
社区成员
发帖
与我相关
我的任务
分享
[Function(Name="dbo.Page_CountSort")]
public int Page_CountSort([Parameter(Name="Table", DbType="VarChar(50)")] string table, [Parameter(Name="Filter", DbType="VarChar(1000)")] string filter, [Parameter(Name="Count", DbType="Int")] System.Nullable<int> count, [Parameter(DbType="VarChar(50)")] string id, [Parameter(Name="CurrPage", DbType="Int")] System.Nullable<int> currPage, [Parameter(DbType="Int")] System.Nullable<int> sortid)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), table, filter, count, id, currPage, sortid);
return ((int)(result.ReturnValue));
}
ALTER proc [dbo].[sp_allpage]--分页显示三级目录商品
@pageSiez int,--页大小
@pageIndex int,--页索引
@tableName varchar(200),--表名
@returnFile varchar(50),--返回字段
@orderFile varchar(50),--排序字段
@orderType varchar(50),--排序类型
@selectWhere varchar(100),--查询条件
@topPage int output --总页数
as
--create table #temptable(tempid int identity(1,1),currentid int) --创建临时表
set nocount on
Begin Tran --开始事务
declare
@mysql nvarchar(1000) ,
@topRecord int --总记录数
--select @topPage=tempid,@topRecord=currentid from #temptable
if(@selectWhere='')
select @mysql='select @sql_topRecord=count(*) from Shop_Goods '+@tableName
else
select @mysql='select @sql_topRecord=count(*) from '+@tableName+' where '+@selectWhere
exec sp_executesql @mysql,N'@sql_topRecord int output',@topRecord output
set @topPage=ceiling(@topRecord*1.0/@pageSiez)
declare @sql varchar(5000)
if(@selectWhere='')
begin
select @sql='select top '+ convert(varchar(20),@pageSiez)+@returnFile+' from '+@tableName
+' where '+@orderFile+' not in(select top '+ convert(varchar(20),(@pageIndex -1)*@pageSiez)
+' '+@orderFile+' from '+@tableName+' order by '+@orderFile+' '+@orderType+')'+' order by '
+@orderFile+' '+@orderType
end
else
begin
select @sql='select top '+convert(varchar(20),@pageSiez)+@returnFile+' from '+@tableName
+' where ('+@orderFile+' not in(select top '+convert(varchar(20),(@pageIndex -1) *@pageSiez)
+' '+@orderFile+' from '+@tableName+' where '+@selectWhere+' order by '+@orderFile+' '
+@orderType+')) and '+@selectWhere+' order by '+@orderFile+' '+@orderType
end
IF (@@rowcount<>1) --执行结果影响行数为0
BEGIN
Print '失败'
ROLLBACK TRANSACTION --回滚
return 0;
END
ELSE
BEGIN
--Print '成功'
COMMIT TRANSACTION --提交事务
return @topRecord
END
print @sql
exec (@sql)