请教在jsp中怎么调用存储过程和执行存储过程？

Question

请教在jsp中怎么调用存储过程和执行存储过程？
ALTER       procedure dbo.Proc_adds(@cpcode varchar(10),@servicecode varchar(20),@servicename varchar(50),@cpname varchar(30),@sendcode varchar(10),@feevalue char(6),@spnumber varchar(20),@provider int,@priority int,@report int) 
as 
declare @mqpath varchar(20),@shortspnumber char(4),@servicecode2 varchar(20),@feetype varchar(5)
set @mqpath='ChinaMobileV3'
set @shortspnumber='5298'
set @servicecode2=@servicecode
set @feetype='02'
--
if @provider=2 
begin
set @mqpath='UnicomV3'
set @shortspnumber='9582'
set @servicecode2=@servicecode+'-lt'
set @feetype='2'
end if not exists (select cpcode from CPBaseInfo where cpcode = @cpcode) 
INSERT INTO [SMS].[dbo].[CPBaseInfo]([CPCode], [Password],[Cpnumber])
VALUES(@cpcode, @cpcode+'123',@shortspnumber); select * from CPbaseinfo where cpcode = @cpcode

if not exists (select cpcode from CPServiceID where cpcode = @cpcode and cpserviceid=@servicecode and servicecode=@servicecode) 
INSERT INTO [SMS].[dbo].[CPServiceID]([CPCode], [CPServiceID], [ServiceCode],[ActionID])
VALUES(@cpcode,@servicecode,@servicecode,1) select * from CPServiceID where cpcode = @cpcode and cpserviceid=@servicecode and servicecode=@servicecode

if not exists (select Servicecode from Servicecode where servicecode=@servicecode2) 
INSERT INTO [SMS].[dbo].[ServiceCode]([ServiceCode], [Name], [RealName], [FeeType], [FeeCode], [SendCode], [Provider], [Priority], [MoCode], [MqPath], [SpNumber], [Report_flag],[Nameorder])
VALUES(@servicecode2, @servicename, @cpname, @feetype, @feevalue, @sendcode,@provider,@priority, '',@mqpath, @spnumber,@report,1)

select * from servicecode where servicecode=@servicecode2
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Answer 1

public Collection select_procedure(String proce_name){
ArrayList list=new ArrayList();
        Connection con=null;
        CallableStatement msProcedure=null;
        ResultSet rs=null;
        try{
        
         con=dbm.getConnection("sqlserver");
         msProcedure=con.prepareCall("{?=call "+proce_name+"}");
         msProcedure.registerOutParameter(1,java.sql.Types.VARCHAR);
         rs=msProcedure.executeQuery();
         int length=rs.getMetaData().getColumnCount();
         while(rs.next()){
String[] strs=new String[length];
for(int j=0;j<length;j++){
strs[j]=rs.getString(j+1);
if(strs[j]!=null)
strs[j]=strs[j].trim();
}
list.add(strs);
}
        rs.close();
        msProcedure.close();
        }catch(Exception ex){
     System.out.println("调用queryComm时:"+ex.toString());
     }
     finally{
     if(con!=null)dbm.freeConnection("sqlserver",con);
     }
     return list;
}

Answer 2

connection.prepareCall("{ call 存储过程名(?, ?) }")

Answer 3

如果有输出参数的话还要registerOutParameter(1, java.sql.Types.XXX);

Answer 4

CallableStatement sql=con.prepareCall("{call dbo.proc_name(?)}");