帮我看看这个存储过程怎么调用吧!谢谢!
我的存储过程是:
CREATE procedure proc_count_ry
@department varchar(50),
@re1 varchar(50)
as
set nocount on
declare @dp varchar(60) ,@rmk varchar(60)
set @dp = '%'+@department + '%'
set @rmk = '%' +@re1 + '%'
SELECT itemcode, department, SUM(monthpeople) AS monthpeople,re1 into #temp FROM cw
where department like @dp and re1 like @rmk
GROUP BY itemcode, department,re1 ORDER BY itemcode,department,re1
SELECT itemcode, department, SUM(monthpeople) AS monthpeople into #temp1 FROM cw
where department like @dp
GROUP BY itemcode, department ORDER BY itemcode,department
select a.itemcode ,a.department,a.monthpeople ,b.monthpeople as total from #temp a ,#temp1 b
where a.itemcode = b.itemcode
set nocount on
GO我在JAVA 写了一段这样的代码 不知道对不对?
public int getproc_count_ry(String department, String re1)
{
//调用存储过程
int ret =0;
Connection cConn = null;
CallableStatement sStmt = null; try
{
cConn = SQLServerManagerFactory.getConnetion();
sStmt = cConn.prepareCall("{ ? = call proc_count_ry (?) }");
sStmt.registerOutParameter(1, java.sql.Types.INTEGER);
sStmt.registerOutParameter(2, java.sql.Types.INTEGER);
sStmt.execute();
SQLServerManagerFactory.close(cConn);
sStmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
return ret;
}
然后 在JSP 页面上怎么调用这个存储过程显示出结果呢??
谢谢!!
我的存储过程是:
CREATE procedure proc_count_ry
@department varchar(50),
@re1 varchar(50)
as
set nocount on
declare @dp varchar(60) ,@rmk varchar(60)
set @dp = '%'+@department + '%'
set @rmk = '%' +@re1 + '%'
SELECT itemcode, department, SUM(monthpeople) AS monthpeople,re1 into #temp FROM cw
where department like @dp and re1 like @rmk
GROUP BY itemcode, department,re1 ORDER BY itemcode,department,re1
SELECT itemcode, department, SUM(monthpeople) AS monthpeople into #temp1 FROM cw
where department like @dp
GROUP BY itemcode, department ORDER BY itemcode,department
select a.itemcode ,a.department,a.monthpeople ,b.monthpeople as total from #temp a ,#temp1 b
where a.itemcode = b.itemcode
set nocount on
GO我在JAVA 写了一段这样的代码 不知道对不对?
public int getproc_count_ry(String department, String re1)
{
//调用存储过程
int ret =0;
Connection cConn = null;
CallableStatement sStmt = null; try
{
cConn = SQLServerManagerFactory.getConnetion();
sStmt = cConn.prepareCall("{ ? = call proc_count_ry (?) }");
sStmt.registerOutParameter(1, java.sql.Types.INTEGER);
sStmt.registerOutParameter(2, java.sql.Types.INTEGER);
sStmt.execute();
SQLServerManagerFactory.close(cConn);
sStmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
return ret;
}
然后 在JSP 页面上怎么调用这个存储过程显示出结果呢??
谢谢!!
2.sStmt.registerOutParameter(1, java.sql.Types.INTEGER);
sStmt.registerOutParameter(2, java.sql.Types.INTEGER); 这里错了吧,你的@department,@re1这两个参数是存储过程的输入参数,而你上面两句是注册输出参数用的,应该是:
sStmt.setString(1,"第一个要传入的字符串参数的值");
sStmt.setString(2,"第二个要传入的字符串参数的值");
<td width="50%" >项目编号</td>
<td width="20%" >当月人天</td> <td width="50%" >项目总和</td>
</tr>
<% request.setCharacterEncoding("gb2312"); String department=request.getParameter("department");
out.println("部门:");
out.println(department);
String re1=request.getParameter("re1");
out.println(re1);
Yuenew netInfo = new Yuenew();
cYue data;
netInfo.getproc_count_ry(department,re1);
for(int i=0;i<netInfo.GetRecordCount();i++)
{
data = (cYue)netInfo.GetRecord(i);
%>
</td>
<td bgcolor="#E1EFFA"><%= data.itemcode %><></td>
<!-- <td bgcolor="#E1EFFA" ></td> -->
<td bgcolor="#E1EFFA" ><%= data.re1 %></td>
<td bgcolor="#E1EFFA"><%= data.monthpeople %></td>
</tr><%}%>
<%
CallableStatement sStmt = null; try
{
cConn = SQLServerManagerFactory.getConnetion();
sStmt = cConn.prepareCall("{ call proc_count_ry (?,?) }");
sStmt.setString(1,"第一个要传入的字符串参数的值");
sStmt.setString(2,"第二个要传入的字符串参数的值");
sStmt.execute(); SQLServerManagerFactory.close(cConn);
sStmt.close();
}
catch (SQLException e)
{
e.printStackTrace();}
%>
直接这样写不就行了