CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUT
AS
SELECT @RecordCount=COUNT(*) FROM MoneyRecord WHERE UserName=@strUserName
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR VARCHAR(500)
if @PageIndex<=0
set @PageIndex=1
select * ,identity(int,1,1) AS SID INTO # FROM MoneyRecord WHERE UserName=@strUserName
SET rowcount @pagesize
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM # where sid>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' order by RecordID desc'
EXEC (@SQLSTR)
set rowcount 0
drop table #GO
@PageIndex INT, --以1开始
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUT
AS
SELECT @RecordCount=COUNT(*) FROM MoneyRecord WHERE UserName=@strUserName
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR VARCHAR(500)
if @PageIndex<=0
set @PageIndex=1
select * ,identity(int,1,1) AS SID INTO # FROM MoneyRecord WHERE UserName=@strUserName
SET rowcount @pagesize
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM # where sid>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' order by RecordID desc'
EXEC (@SQLSTR)
set rowcount 0
drop table #GO
解决方案 »
- 求SQL语句
- 求SQL查询语句。基础
- 请教怎样使用sql server 2008的全文搜索功能搜索pdf类型文件
- 想了一个小时没想明白的错误
- 大家好,问一个SQLServer 2005 update 的问题
- SQL中如何不显示系统表?
- 一個table的變幻
- 跪求高手支招
- 一个难对应的考勤明细表的存储过程(400分的题,另开页加分300;完成后再加分,这个表对我来说太重要了,请来帮忙)
- 怎么求出刚刚插入的一条数据的主键值!特急!!问题一解,立马结贴!!!!!!!!!!!!!!!1
- sqlserver 如何实现 javascript 中的 unescape功能!
- 为什么输入数字能查询出来,输入汉字就出错呢?大家帮我看看
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUT
AS
DECLARE @SQLSTR NVARCHAR(500) set @sqlstr='SELECT @RecordCount=COUNT(*) FROM MoneyRecord '+@strUserName
exec sp_executesql @sqlstr,N'@recordcount int output',@recordcount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
if @PageIndex<=0
set @PageIndex=1
select * ,identity(int,1,1) AS SID INTO # FROM MoneyRecord WHERE UserName=@strUserName
SET rowcount @pagesize
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM # where sid>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' order by RecordID desc'
EXEC (@SQLSTR)
set rowcount 0
drop table #GO
运行时提示错误:
无法使用 SELECT INTO 语句向表 '#' 中添加标识列,该表中已有继承了标识属性的列 'RecordID'。
to: wgsasd311(自强不息)
运行时提示错误:
无法使用 SELECT INTO 语句向表 '#' 中添加标识列,该表中已有继承了标识属性的列 'RecordID'。
*/
--另外,能不能把这个存储过程中的strUserName改为strWhere(搜索条件)
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strUserName VARCHAR(30),
@RecordCount INT OUT,
@PageCount INT OUT
AS
DECLARE @SQLSTR NVARCHAR(500) set @sqlstr='SELECT @RecordCount=COUNT(*) FROM MoneyRecord '+@strUserName
exec sp_executesql @sqlstr,N'@recordcount int output',@recordcount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
if @PageIndex<=0
set @PageIndex=1
select *,sid=(select count(1) from moneyrecord where username=@strusername and RecordID<=a.RecordID) into #
FROM MoneyRecord a WHERE UserName=@strUserName
SET rowcount @pagesize
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyWords,Others FROM # where sid>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' order by RecordID desc'
EXEC (@SQLSTR)
set rowcount 0
drop table #GO