我自己做了一个,没有问题,你可以看一下
use Northwind
go
create proc test
@StartOrderID int,
@EndOrderID int,
@Code varchar(1000) Out
As
Begin
Declare @tmp int
Set @Code=''
Declare #cur_orders cursor for Select OrderID From Orders
where OrderID>=@startOrderID and OrderID<=@EndOrderID
for read only
Open #cur_Orders
fetch next from #cur_orders into @tmp
while @@fetch_Status=0
Begin
Set @Code=@Code+'-'+convert(varchar(8),@tmp)
fetch next from #cur_orders into @tmp
End
close #cur_Orders
Deallocate #cur_Orders
return
End
go
use Northwind
go
create proc test
@StartOrderID int,
@EndOrderID int,
@Code varchar(1000) Out
As
Begin
Declare @tmp int
Set @Code=''
Declare #cur_orders cursor for Select OrderID From Orders
where OrderID>=@startOrderID and OrderID<=@EndOrderID
for read only
Open #cur_Orders
fetch next from #cur_orders into @tmp
while @@fetch_Status=0
Begin
Set @Code=@Code+'-'+convert(varchar(8),@tmp)
fetch next from #cur_orders into @tmp
End
close #cur_Orders
Deallocate #cur_Orders
return
End
go
解决方案 »
- 新人求助 谁能给我讲个MVC简单的例子啊 最好带上源码和DAO啊 谢谢
- 如何在j2ee中实时监听并触发相关功能
- hibernate 映象关系配置
- !!!急:webwork 2.0中如何实现从一个action跳转到不同的action
- Hibernate建模工具CowNewStudio1.1.0 发布
- 求教高手如何读取hibernate.xml中的值,高手请进!!!!!在线等带!!!分不是问题!!
- 如何得到i的位置
- 使用java.nio的问题:怎样将ByteBuffer转换为Object?
- ejb什么时候用呢?
- ------------------- 分割字符串的问题 ---------------------------
- 想做一個WEB方式的OUTLOOK,請問JAVAMAIL怎樣創建和操作本地的收件箱,發件箱????
- rmi"生成"求救
String ret=null;
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url
="jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=Northwind";
String user="sa";
String password="";
Connection conn= DriverManager.getConnection(url,user,password);
CallableStatement stmt=conn.prepareCall("exec test ?,?,?");
stmt.setInt(1,10248);
stmt.setInt(2,10284);
stmt.registerOutParameter(3,Types.VARCHAR);
stmt.setString(3,ret);
stmt.execute();
System.out.println(stmt.getString(3));
stmt.close();
stmt=null;
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
真的很感谢你能写了一个列子来验证我的问题!thank you Djava!
Create table tmpOrders (
OrderID int,
CustomerID nchar(5)
)--把Orders 里的OrderID列全部插入,这样Orders与tmpOrders之间就是1:1关系了
insert into tmpOrders
Select distinct orderID,'tmp' from Orderscreate proc test
@StartOrderID int,
@EndOrderID int,
@Code varchar(1000) Out
As
Begin
Declare @newOrderID int
Declare @newCustomerID nchar(5)
Declare @DummyInt int
Declare @DummyChar nchar(5)
Set @Code=''
/*
1:1
temp table/formal table is synchronized tmpOrders <---> Orders
fetch from Orders, update tmpOrders
*/ -- for temp table
Declare #cur_tmpOrders Cursor for select OrderID,CustomerID
From tmpOrders
where OrderID>=@startOrderID
and OrderID<=@EndOrderID
for update --for formal table
Declare #cur_orders cursor for Select OrderID,CustomerID
From Orders
where OrderID>=@startOrderID
and OrderID<=@EndOrderID
for read only
Open #cur_Orders
Open #cur_tmpOrders fetch next from #cur_tmpOrders into @DummyInt,@dummyChar --Important!!!
fetch next from #cur_orders into @NewOrderID,@NewCustomerID
while @@fetch_Status=0
Begin
--Set @Code=@Code+'-'+convert(varchar(8),@NewOrderID)
--update tempOrders use corresponding Orders' data
Update tmpOrders set customerID=@newCustomerID
where current of #cur_tmpOrders
--pay attention to sequence of cursor fetch action!
fetch next from #cur_tmpOrders into @DummyInt,@dummyChar
if @@fetch_Status<>0 break; -- 没有行了
fetch next from #cur_orders into @newOrderID,@NewCustomerID
End close #cur_Orders
close #cur_tmpOrders Deallocate #cur_Orders
Deallocate #cur_tmpOrders
Set @Code='Ok'
return
End
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url=
"jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=Northwind";
String user="sa";
String password="";
Connection conn= DriverManager.getConnection(url,user,password);
CallableStatement stmt=conn.prepareCall("exec test ?,?,?");
stmt.setInt(1,10248);
stmt.setInt(2,10284);
stmt.registerOutParameter(3,Types.VARCHAR,1000);
stmt.setString(3,ret);
stmt.executeUpdate();
System.out.println(stmt.getString(3));
stmt.close();
stmt=null;
conn.close();
conn=null;
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}