CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strwhere VARCHAR(100),
@RecordCount INT OUT,
@PageCount INT OUT
AS
DECLARE @SQLSTR NVARCHAR(500)
if ltrim(@strwhere)=''
set @sqlwhere='1=1'set @sqlstr='SELECT @RecordCount=COUNT(*) FROM MoneyRecord WHERE '+@strwhere
exec sp_executesql @sql,'@recordcount int output',@recordcount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
if @PageIndex<=0
set @PageIndex=1
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyword,Others FROM [MoneyRecord] t'
+' where (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' and '+@strwhere
+' order by RecordID desc'
EXEC (@SQLSTR)
GO
@PageIndex INT, --以1开始
@PageSize INT,
@strwhere VARCHAR(100),
@RecordCount INT OUT,
@PageCount INT OUT
AS
DECLARE @SQLSTR NVARCHAR(500)
if ltrim(@strwhere)=''
set @sqlwhere='1=1'set @sqlstr='SELECT @RecordCount=COUNT(*) FROM MoneyRecord WHERE '+@strwhere
exec sp_executesql @sql,'@recordcount int output',@recordcount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
if @PageIndex<=0
set @PageIndex=1
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyword,Others FROM [MoneyRecord] t'
+' where (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' and '+@strwhere
+' order by RecordID desc'
EXEC (@SQLSTR)
GO
@PageIndex INT, --以1开始
@PageSize INT,
@strwhere VARCHAR(100),
@RecordCount INT OUT,
@PageCount INT OUT
AS
DECLARE @SQLSTR NVARCHAR(500)
if ltrim(@strwhere)=''
set @strwhere='1=1'set @SQLSTR='SELECT @RecordCount=COUNT(*) FROM MoneyRecord WHERE '+@strwhere
exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
if @PageIndex<=0
set @PageIndex=1
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyword,Others FROM [MoneyRecord] t'
+' where (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' and '+@strwhere
+' order by RecordID desc'
EXEC (@SQLSTR)
GO
有三个问题:
1、我调试的时候输入参数PageIndex =1 PageSize=5 strwhere=""
程序进行到exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output时报错:过程需要参数 '@parameters' 为 'ntext/nchar/nvarchar' 类型。2、SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyword,Others FROM [MoneyRecord] t'
+' where (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' and '+@strwhere
+' order by RecordID desc'
中倒数第二行 +' and '+@strwhere 是不是应该是: +' and Where'+@strwhere 3、 +' where (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
中的UserID=t.UserID 需要不需要做相应的修改呢?
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strwhere NVARCHAR(100),
@RecordCount INT OUT,
@PageCount INT OUT
AS
DECLARE @SQLSTR NVARCHAR(500)
if ltrim(@strwhere)=''
set @strwhere='1=1'
set @strwhere=replace(@strwhere,'''','''''')set @SQLSTR=N'SELECT @RecordCount=COUNT(*) FROM MoneyRecord WHERE '+@strwhere
exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
if @PageIndex<=0
set @PageIndex=1
SET @SQLSTR=N'select top '+convert(varchar,@PageSize)
+N' RecordID,myDateTime,MoneyType,MoneyNum,myKeyword,Others FROM [MoneyRecord] t'
+N' where (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(Nvarchar,@PageSize*(@PageIndex-1))
+N' and '+@strwhere
+N' order by RecordID desc'
EXEC (@SQLSTR)
GO
执行到:
exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output提示:
Microsoft][ODBC SQL Server Driver][SQL Server]过程需要参数 '@parameters' 为 'ntext/nchar/nvarchar' 类型。
执行到:
exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output提示:
Microsoft][ODBC SQL Server Driver][SQL Server]过程需要参数 '@parameters' 为 'ntext/nchar/nvarchar' 类型。