CREATE PROCEDURE [dbo].[getDataByPage] (
@queryStr nvarchar(1000),
@keyField nvarchar(200),
@sort bit,
@fsort nvarchar(1000),
@pageSize int,
@pageNumber int,
@counts int = 1 output)AS
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
DECLARE @SQLText2 AS nvarchar(4000)
DECLARE @sqlSort AS nvarchar(4000) if @Sort=1
begin
if not(@fSort is null)
set @sqlSort = ' Order by ' + @fSort
else
set @sqlSort = ' Order by ' + @keyField
end
else
begin
if not(@fSort is null)
set @sqlSort = ' Order by ' + @fSort + ' DESC '
else
set @sqlSort = ' Order by ' + @keyField + ' DESC '
end SET @SQLText2 = 'SELECT @counts=Count(' + @keyField + ') ' + @queryStr
exec sp_executesql @SQLText2,N'@counts int out',@counts out SET @SQLTable ='SELECT TOP ' + CAST((@pageNumber*@pageSize) AS varchar(30)) + ' ' +' * ' +@queryStr + ' ' + @sqlSort
Set @SQLText = 'Select Top ' + Cast(@PageSize AS varchar(30)) + ' * from ' +'('+@SQLTable+')As TembTbA '+' Where '+@KeyField+' Not In (Select Top ' + CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '+'('+@SQLTable+') AS TempTbB)'
Exec(@SQLText)
GO报错:java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().
@queryStr nvarchar(1000),
@keyField nvarchar(200),
@sort bit,
@fsort nvarchar(1000),
@pageSize int,
@pageNumber int,
@counts int = 1 output)AS
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
DECLARE @SQLText2 AS nvarchar(4000)
DECLARE @sqlSort AS nvarchar(4000) if @Sort=1
begin
if not(@fSort is null)
set @sqlSort = ' Order by ' + @fSort
else
set @sqlSort = ' Order by ' + @keyField
end
else
begin
if not(@fSort is null)
set @sqlSort = ' Order by ' + @fSort + ' DESC '
else
set @sqlSort = ' Order by ' + @keyField + ' DESC '
end SET @SQLText2 = 'SELECT @counts=Count(' + @keyField + ') ' + @queryStr
exec sp_executesql @SQLText2,N'@counts int out',@counts out SET @SQLTable ='SELECT TOP ' + CAST((@pageNumber*@pageSize) AS varchar(30)) + ' ' +' * ' +@queryStr + ' ' + @sqlSort
Set @SQLText = 'Select Top ' + Cast(@PageSize AS varchar(30)) + ' * from ' +'('+@SQLTable+')As TembTbA '+' Where '+@KeyField+' Not In (Select Top ' + CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '+'('+@SQLTable+') AS TempTbB)'
Exec(@SQLText)
GO报错:java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().
@queryStr nvarchar(1000),
@keyField nvarchar(200),
@sort bit,
@fsort nvarchar(1000),
@pageSize int,
@pageNumber int,
@counts int = 1 output) AS
--加上这一句试试******************
set nocount on
---------------------------------
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
DECLARE @SQLText2 AS nvarchar(4000)
DECLARE @sqlSort AS nvarchar(4000) if @Sort=1
begin
if not(@fSort is null)
set @sqlSort = ' Order by ' + @fSort
else
set @sqlSort = ' Order by ' + @keyField
end
else
begin
if not(@fSort is null)
set @sqlSort = ' Order by ' + @fSort + ' DESC '
else
set @sqlSort = ' Order by ' + @keyField + ' DESC '
end SET @SQLText2 = 'SELECT @counts=Count(' + @keyField + ') ' + @queryStr
exec sp_executesql @SQLText2,N'@counts int out',@counts out SET @SQLTable ='SELECT TOP ' + CAST((@pageNumber*@pageSize) AS varchar(30)) + ' ' +' * ' +@queryStr + ' ' + @sqlSort
Set @SQLText = 'Select Top ' + Cast(@PageSize AS varchar(30)) + ' * from ' +'('+@SQLTable+')As TembTbA '+' Where '+@KeyField+' Not In (Select Top ' + CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '+'('+@SQLTable+') AS TempTbB)' Exec(@SQLText)
GO 报错:java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().
和java的调用方式有关吗?
应该和java的调用没关系,因为它的错误类型是java.sql.SQLException:
而且Output parameters have not yet been processed. Call getMoreResults(). 好像是说输出参数没有被处理。那应该是指存储过程里面的问题。
然后把这个语句放在查询分析器里执行下,就知道了。
Select Top 4 * from (SELECT TOP 4 * from jp_news Order by addtime DESC) As TembTbA Where id Not In (Select Top 0 id From (SELECT TOP 4 * from jp_news Order by addtime DESC) AS TempTbB)declare @P1 int
set @P1=5
exec getDataByPage N' from jp_news ', N'id', 0, N'addtime', 4, 1, @P1 output
select @P1RPC Output Paameter
5
这个是追踪的结果output为5没错啊。而且那个sql语言貌似也没语法错误
declare @P1 int
set @P1=7
exec getDataByPage N' from jp_news ', N'id', 0, N'addtime', 4, 1, @P1 output
select @P1是否存储过程没返回resultset???只返回了那个output?
public List<News> qNews(Spage spage){
List<News> list = new ArrayList<News>(); // new一个list容器来保存查询结果
String query = "{ CALL getDataByPage(?, ? ,? ,? ,? ,? ,?)}";
CallableStatement cstmt = null;
cstmt = db.callablestmt(query);
try{
cstmt.registerOutParameter(7, Types.INTEGER);
cstmt.setString(1, spage.getqueryStr());
cstmt.setString(2, spage.getKeyField());
cstmt.setInt(3, spage.getSort());
cstmt.setString(4, spage.getFsort());
cstmt.setInt(5, spage.getPageSize());
cstmt.setInt(6, spage.getPageNum());
cstmt.execute();
ResultSet rs = null;
rs = cstmt.getResultSet();
int counts = cstmt.getInt(7);
System.out.println(counts);
while(rs.next()){
News news = new News();
news.setId(rs.getInt("id"));
news.setTitle(rs.getString("title"));
news.setContent(rs.getString("content"));
news.setTime(rs.getString("addtime"));
list.add(news);
}
} catch (SQLException e) {
System.out.println("-----------------------Manager.qNews()方法异常-------------");
e.printStackTrace();
} finally{
db.closeCstmt(cstmt);
}
return list;
}
这个是java代码。给大家看看
这行报错吗?