62,052
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
iwanghs 2010-12-24
- 打赏
- 举报
等下啊给你找找,一会发
liuwei2500 2010-12-24
- 打赏
- 举报
CREATE PROCEDURE Pageing
(
@TableName varchar(50),
@PageSize int = 20,
@PageIndex int = 1,
@ColumnName varchar(255)='',
@Where varchar(200)='',
@State int = 0 output,
@PageCount int = 0 output
)
AS
BEGIN
DECLARE @ResultCount int;----------------------------------------总记录数
SET @ResultCount = 0;
DECLARE @Sql varchar(5000);
SET @Sql ='';
DECLARE @mod int;-------------------------------------------------------记录总记录数%pagesize
SET @mod = 0;
DECLARE @Integer int;------------------------------------------------- 记录总记录数/pagesize
SET @Integer = 0;
IF (@TableName ='' OR @TableName = NULL)
BEGIN
set @State = 1; --表示未输入表名
GOTO EndProcedure;
END
IF (@ColumnName ='' OR @ColumnName = NULL)
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TB_TempTableColumn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TB_TempTableColumn]
EXEC('SELECT name as strCOLUMN INTO TB_TempTableColumn FROM syscolumns s WHERE (s.id=(SELECT id FROM sysobjects s2 WHERE s2.name='''+@TableName+''')) AND (s.colid = 1)');
--SET @Sql = 'SELECT name as strCOLUMN INTO TB_TempTableColumn FROM syscolumns s WHERE (s.id=(SELECT id FROM sysobjects s2 WHERE s2.name='''+@TableName+''')) AND (s.colid = 1)';
PRINT(@Sql);
SET @ColumnName = (SELECT TOP 1 strCOLUMN FROM TB_TempTableColumn);
drop table [dbo].[TB_TempTableColumn]
--SET @State =2;
--GOTO EndProcedure;
END
----------------------------得到表中的记录总数---------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TB_TempCountResult]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[TB_TempCountResult]
IF(@Where = '' OR @Where =NULL )
BEGIN
EXEC('SELECT COUNT(*) as CountResult INTO TB_TempCountResult FROM ' + @TableName);
END
ELSE
BEGIN
EXEC('SELECT COUNT(*) as CountResult INTO TB_TempCountResult FROM ' + @TableName + ' where (' +@Where+')');
END
SET @ResultCount = (SELECT TOP 1 CountResult FROM TB_TempCountResult);
drop table [dbo].[TB_TempCountResult]
----------------------------------------------------------------------------------------------------------------
SET @mod = @ResultCount % @PageSize;
SET @Integer = @ResultCount / @PageSize;
IF(@ResultCount = 0)
BEGIN
SET @State = 3;------------------------------------------------总记录数为0
GOTO EndProcedure;
END
IF(@mod <> 0)
BEGIN
SET @PageCount = @Integer +1; -----得到总记录数
END
IF(@PageIndex >@PageCount)
BEGIN
SET @State =4; --------------------------------------------当前页已经超过了总页数
GOTO EndProcedure;
END
IF(@PageCount =1)----------------------------------------------------总页数只有一页
BEGIN
IF(@Where = '' OR @Where = NULL)
BEGIN
SET @Sql ='select * from '+@TableName;
END
ELSE
BEGIN
SET @Sql ='select * from '+@TableName + ' where ( ' +@Where +')';
END
END
ELSE IF(@PageIndex = 1)-----------------------------------------第一页
BEGIN
IF(@Where = '' OR @Where = NULL)
BEGIN
SET @sql = 'select top '+ cast(@PageSize AS varchar(10)) + ' * from '+@TableName;
END
ELSE
BEGIN
SET @sql = 'select top '+ cast(@PageSize AS varchar(10)) + ' * from '+@TableName + ' where ( ' +@Where +')';
END
END
ELSE------------------------------------------------------------------------其余任何页
BEGIN
IF(@Where = '' OR @Where = NULL)
BEGIN
SET @Sql= 'select top '+ cast(@PageSize AS varchar(10)) + ' * from '+@TableName + ' where ('+@ColumnName+' not in (select top '
+cast(@PageSize*(@PageIndex - 1) AS varchar(10))+' '+@ColumnName+' from '+@TableName+' order by '+@ColumnName+' desc)) order by '+@ColumnName+' desc';
END
ELSE
BEGIN
SET @Sql= 'select top '+ cast(@PageSize AS varchar(10)) + ' * from '+@TableName + ' where ('+@ColumnName+' not in (select top '
+cast(@PageSize*(@PageIndex - 1) AS varchar(10))+' '+@ColumnName+' from '+@TableName+' where ('+@Where+') order by '+@ColumnName+' desc)) and ( ' +@Where +') order by '+@ColumnName + ' desc';
END
END
--set @ResultCount = @@ROWCOUNT
PRINT (@Sql)
EndProcedure:
PRINT(@State)
--PRINT('test');
END
GO
Dylan-Wang 2010-12-24
- 打赏
- 举报
5楼的正解，上学的时候就是这么写的，哈哈
指间、魔法师 2010-12-23
- 打赏
- 举报
luyuwei2008 2010-12-23
- 打赏
- 举报
百度一下
TENGFEI3636 2010-12-23
- 打赏
- 举报
ALTER PROCEDURE [dbo].[Queryuserinfo]
--
@num int,--每页多少条数据
@pag int,--第几页
@LastLogonDate varchar(31),
@LastLogonIP varchar(15),
@count int output
as
begin
---通过ip或者最后登录日期查询用户登录所有信息
select * from(
select ROW_NUMBER() Over(order by RegAccounts) as suma ,a.UserID,a.RegAccounts,b.Score,b.InsureScore, (Score+InsureScore) as resut,
convert(varchar(20),a.LastLogonDate,120) as LastLogonDate,a.LastLogonIP from QPWapDB.[dbo].[My_Logon_Logoned] as a, QPTreasureDB.dbo.GameScoreInfo as b
where a.LastLogonIP=@LastLogonIP or a.LastLogonDate = @LastLogonDate
) as newstable
where suma between (@pag-1)*@num and @num*@pag
end
最简单了
--
@num int,--每页多少条数据
@pag int,--第几页
@LastLogonDate varchar(31),
@LastLogonIP varchar(15),
@count int output
as
begin
---通过ip或者最后登录日期查询用户登录所有信息
select * from(
select ROW_NUMBER() Over(order by RegAccounts) as suma ,a.UserID,a.RegAccounts,b.Score,b.InsureScore, (Score+InsureScore) as resut,
convert(varchar(20),a.LastLogonDate,120) as LastLogonDate,a.LastLogonIP from QPWapDB.[dbo].[My_Logon_Logoned] as a, QPTreasureDB.dbo.GameScoreInfo as b
where a.LastLogonIP=@LastLogonIP or a.LastLogonDate = @LastLogonDate
) as newstable
where suma between (@pag-1)*@num and @num*@pag
end
最简单了
Cool_xiaocao 2010-12-23
- 打赏
- 举报
通用分页存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[Proc_Pag]
(
@sql nvarchar(4000),--查询sql语句包括条件
@key nvarchar(100), --排序字段
@PageIndex int, --当前页码
@PageSize int, --每页显示数
@count int output --总共行数
)
AS
declare @strSql nvarchar(4000) --查询的sql语句
declare @strCount nvarchar(4000) --查询总记录数的sql语句
declare @c int
BEGIN
--数据集
set @strSql='WITH list AS ('+@sql+') SELECT * FROM (SELECT ROW_NUMBER() OVER (order by '+ @key+') RowNumber,* FROM list) t where RowNumber between ' + convert(varchar(20), ((@PageIndex - 1) * @PageSize + 1)) + ' and ' + convert(varchar(20),(@PageIndex * @PageSize))
exec(@strSql)
--总条数
set @strCount='WITH list AS ('+@sql+')
select @c=count(*) from list '
exec sp_executesql @strCount,N'@c int output',@count output
END
xuan.ye 2010-12-23
- 打赏
- 举报
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[Proc_Pag]
(
@sql nvarchar(4000),--查询sql语句包括条件
@key nvarchar(100), --排序字段
@PageIndex int, --当前页码
@PageSize int, --每页显示数
@count int output --总共行数
)
AS
declare @strSql nvarchar(4000) --查询的sql语句
declare @strCount nvarchar(4000) --查询总记录数的sql语句
declare @c int
BEGIN
--数据集
set @strSql='WITH list AS ('+@sql+') SELECT * FROM (SELECT ROW_NUMBER() OVER (order by '+ @key+') RowNumber,* FROM list) t where RowNumber between ' + convert(varchar(20), ((@PageIndex - 1) * @PageSize + 1)) + ' and ' + convert(varchar(20),(@PageIndex * @PageSize))
exec(@strSql)
--总条数
set @strCount='WITH list AS ('+@sql+')
select @c=count(*) from list '
exec sp_executesql @strCount,N'@c int output',@count output
END
-----------------
这个简单，什么都不用管，就是分
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[Proc_Pag]
(
@sql nvarchar(4000),--查询sql语句包括条件
@key nvarchar(100), --排序字段
@PageIndex int, --当前页码
@PageSize int, --每页显示数
@count int output --总共行数
)
AS
declare @strSql nvarchar(4000) --查询的sql语句
declare @strCount nvarchar(4000) --查询总记录数的sql语句
declare @c int
BEGIN
--数据集
set @strSql='WITH list AS ('+@sql+') SELECT * FROM (SELECT ROW_NUMBER() OVER (order by '+ @key+') RowNumber,* FROM list) t where RowNumber between ' + convert(varchar(20), ((@PageIndex - 1) * @PageSize + 1)) + ' and ' + convert(varchar(20),(@PageIndex * @PageSize))
exec(@strSql)
--总条数
set @strCount='WITH list AS ('+@sql+')
select @c=count(*) from list '
exec sp_executesql @strCount,N'@c int output',@count output
END
-----------------
这个简单，什么都不用管，就是分
yzf86211861 2010-12-23
- 打赏
- 举报
create PROCEDURE [dbo].[news_Showlist]
(
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int , -- 页尺寸
@PageIndex int , -- 页码
@strWhere varchar(1500), -- 查询条件(注意: 不要加where)
@Sort varchar(255) --排序的方法
)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @Sort = 'desc'
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName
+ ' where ' + @strWhere + ' ' + @strOrder
end
else
begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName
+ ' '+ @strOrder
end
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
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
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
end
exec (@strSQL)
RETURN
用法 exec news_Showlist 'Person A inner join sad B on A.id=B.Kid ', '*','A.name', 10,1,'1=1','desc'
就是这过程有点老 等大牛给个 好的
