我的存储过程的功能是先获取当天日期(月日),比如今天是2008-07-17,那么月日就是0717,然后到DB_CustomSMS.dbo.tbl_SMResult_0717表中去查找MsgID=输入参数的值,如果没有一条记录,则到日期是上一天的表中去查询,如果找到就返回1,如果连续30天都找不到一条记录等于输入参数,那么就返回0
存储过程代码如下:
------------------------------------------------------------------------------------------------------
CREATE procedure query_date @MsgID varchar(22)
AS
Declare
@month char(2),
@day char(2),
@tableName varchar(150),
@datestr varchar(10),
@curdate datetime,
@sql varchar(100),
@i int
set @curdate=getdate()
set @i=1 while (@i <=30)
begin
--print @curdate
set @datestr=convert(varchar(10),@curdate,112)
-- print @datestr set @month=substring(@datestr,5,2)
--print @month
set @day=substring(@datestr,7,2)
--print @day
set @tableName='DB_CustomSMS.dbo.tbl_SMResult_'+@month+@day
--print @tableName
set @sql=' Select * from '+ @tableName+ ' where MsgID='+@MsgID
print @sql
exec(@sql)
if @@rowcount=0
set @curdate=@curdate-1
else
return 1
set @i=@i+1
end return 0
GO --------------------------------------------------------------------------------
java调用如下:
外部参数smID=10
public void callProcedure(long smID)
{
Connection conn=getConnection();
String query = "{? =call dbo.query_date(?)}";
st = conn.prepareCall(query);
st.registerOutParameter(1, java.sql.Types.INTEGER);
// st.setInt(1, 0);
System.out.println("smID=" + smID);
st.setString(2, String.valueOf(smID));
System.out.println("执行前retValue= " + retValue);
st.execute();
retValue = st.getInt(1);
System.out.println("执行后retValue= " + retValue);
st.close();
conn.close();
if (retValue == 1) {
System.out.println("找到回执短信");
} else {
System.out.println("没有找到一条回执短信");
}
}
--------------------------------------------------------------------------------------- 运行结果始终是:
执行前retValue= -1
执行后retValue= 0
没有找到一条回执短信
---------------------
我在数据库中表DB_CustomSMS.dbo.tbl_SMResult_0715中设置了
MsgID=10的记录了
结果应该返回1才对啊,可是为什么总是得不到1呢?
哪位大哥帮忙解决一下啊,多谢了!
存储过程代码如下:
------------------------------------------------------------------------------------------------------
CREATE procedure query_date @MsgID varchar(22)
AS
Declare
@month char(2),
@day char(2),
@tableName varchar(150),
@datestr varchar(10),
@curdate datetime,
@sql varchar(100),
@i int
set @curdate=getdate()
set @i=1 while (@i <=30)
begin
--print @curdate
set @datestr=convert(varchar(10),@curdate,112)
-- print @datestr set @month=substring(@datestr,5,2)
--print @month
set @day=substring(@datestr,7,2)
--print @day
set @tableName='DB_CustomSMS.dbo.tbl_SMResult_'+@month+@day
--print @tableName
set @sql=' Select * from '+ @tableName+ ' where MsgID='+@MsgID
print @sql
exec(@sql)
if @@rowcount=0
set @curdate=@curdate-1
else
return 1
set @i=@i+1
end return 0
GO --------------------------------------------------------------------------------
java调用如下:
外部参数smID=10
public void callProcedure(long smID)
{
Connection conn=getConnection();
String query = "{? =call dbo.query_date(?)}";
st = conn.prepareCall(query);
st.registerOutParameter(1, java.sql.Types.INTEGER);
// st.setInt(1, 0);
System.out.println("smID=" + smID);
st.setString(2, String.valueOf(smID));
System.out.println("执行前retValue= " + retValue);
st.execute();
retValue = st.getInt(1);
System.out.println("执行后retValue= " + retValue);
st.close();
conn.close();
if (retValue == 1) {
System.out.println("找到回执短信");
} else {
System.out.println("没有找到一条回执短信");
}
}
--------------------------------------------------------------------------------------- 运行结果始终是:
执行前retValue= -1
执行后retValue= 0
没有找到一条回执短信
---------------------
我在数据库中表DB_CustomSMS.dbo.tbl_SMResult_0715中设置了
MsgID=10的记录了
结果应该返回1才对啊,可是为什么总是得不到1呢?
哪位大哥帮忙解决一下啊,多谢了!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货