一位高人给了下面的代码:
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 @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
------------------我调试的时候输入参数PageIndex =1 PageSize=5 strwhere=""
程序进行到exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output时报错:过程需要参数 '@parameters' 为 'ntext/nchar/nvarchar' 类型。exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output
就是这句话有问题,其它都好
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 @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
------------------我调试的时候输入参数PageIndex =1 PageSize=5 strwhere=""
程序进行到exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output时报错:过程需要参数 '@parameters' 为 'ntext/nchar/nvarchar' 类型。exec sp_executesql @sqlstr,'@recordcount int output',@recordcount output
就是这句话有问题,其它都好
解决方案 »
- update误操作,怎么能恢复?
- 我有一个数据库300m左右,如何把它导入到sql 2000数据库中呢?
- 如何让select * from 表 with ('UDTLOCK')不返回行集
- 请教一个难解的问题,不知sql语句能否满足这样的查询要求?
- 何处有SQL SERVER 下载???
- 将数据类型 varchar 转换为 numeric 时出错
- SQL SERVER 2005数据库表里的时间比系统时间晚8个小时
- 请教各位高手:SQL的登陆问题~~~!!!!!救急啊!!~~~
- 如何修复SQL SERVER数据!
- 数据库应用实例,急求
- 讨论自定义字段的设计问题
- 表A中一字段fun,其中有些数据如0112,0113,以0开头,请问怎么写一sql主句,统一把前面的0去掉?谢谢!
ALTER PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strWhere VARCHAR(100),
@RecordCount INT OUT,
@PageCount INT OUT
AS
declare @sql nvarchar(4000)
set @sql=N'select @RecordCount=count(*) FROM MoneyRecord where '+@strWhere+' '
exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR VARCHAR(500)
if @PageIndex<=0
set @PageIndex=1
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyword,Others FROM [MoneyRecord] t'
+' where '+@strWhere+' and (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' '
+' order by RecordID desc'EXEC (@SQLSTR)
GO
select @sql='select 语句'
exec(@sql)
是可以实现的
谢谢楼上两位再加一个小功能
再传出一个参数 totalnum,把表中满足@strwhere条件的条目中的MoneyNum求和返回
有一点:moneytype=1 表示收入,moneytype=0表示支出
这个求和需要求差值,即 totalnum=总收入-总支出
服务器: 消息 170,级别 15,状态 1,行 1
[Microsoft][ODBC SQL Server Driver][SQL Server]第 1 行: 'UserID = 4 AND MoneyType=1 AND myDateTime> '2005-11-18' AND myDateTime< '2005-11-21'' 附近有语法错误。
获取调用堆栈失败!怎么回事啊
ALTER PROCEDURE [GetCustomersDataPage]
@PageIndex INT, --以1开始
@PageSize INT,
@strWhere VARCHAR(2000),
@RecordCount INT OUT,
@PageCount INT OUT
AS
declare @sql nvarchar(4000)
set @sql=N'select @RecordCount=count(*) FROM MoneyRecord where '+@strWhere+' '
exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR VARCHAR(500)
if @PageIndex<=0
set @PageIndex=1
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyword,Others FROM [MoneyRecord] t'
+' where '+@strWhere+' and (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' '
+' order by RecordID desc'EXEC (@SQLSTR)
GO
你的这个可以运行
可以不可以再帮我看看下面的问题:1、再加一个小功能
再传出一个参数 totalnum,把表中满足@strwhere条件的条目中的MoneyNum求和返回
有一点:moneytype=1 表示收入,moneytype=0表示支出
这个求和需要求差值,即 totalnum=总收入-总支出
还有一个问题:"UserID = 4 AND MoneyType=1 AND myDateTime> '2005-11-18' AND myDateTime< '2005-11-21'"这个句子作为strwhere输入执行的时候提示
服务器: 消息 170,级别 15,状态 1,行 1
[Microsoft][ODBC SQL Server Driver][SQL Server]第 1 行: 'UserID = 4 AND MoneyType=1 AND myDateTime> '2005-11-18' AND myDateTime< '2005-11-21'' 附近有语法错误。
获取调用堆栈失败!
@PageIndex INT, --以1开始
@PageSize INT,
@strWhere VARCHAR(2000),
@RecordCount INT OUT,
@PageCount INT OUT,
@totalnum int out
AS
declare @sql nvarchar(4000)
set @sql=N'select @RecordCount=count(*) FROM MoneyRecord where '+@strWhere+' '
exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR VARCHAR(4000)
if @PageIndex<=0
set @PageIndex=1
SET @SQLSTR='select top '+convert(varchar,@PageSize)
+' RecordID,myDateTime,MoneyType,MoneyNum,myKeyword,Others FROM [MoneyRecord] t'
+' where '+@strWhere+' and (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' '
+' order by RecordID desc'EXEC (@SQLSTR)declare @sql2 nvarchar(4000)
set @sql=N'select top '+convert(varchar,@PageSize)
+' @totalnum=sum(case when MoneyType=1 then MoneyNum else -MoneyNum end) FROM [MoneyRecord] t'
+' where '+@strWhere+' and (select count(1) from [MoneyRecord] where RecordID>t.RecordID and UserID=t.UserID)+1>'
+convert(varchar,@PageSize*(@PageIndex-1))
+' '
+' order by RecordID desc'
exec sp_executesql @sql,N'@totalnum int output',@totalnum output
GO
-------------------------
虽然没有解决,但先给分吧
我另外开辟新帖子问大家