下面是我2005中的存储过程set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[spqMemOSPointsDtil]
(
@pMemCode nvarchar(12),
@pStatusDesc nvarchar(50) output
)
AS
BEGIN
SET NOCOUNT ON CREATE TABLE #TempList
(
rSiteNo int,
rSiteName nvarchar(30) COLLATE Chinese_Taiwan_Stroke_CI_AS null,
rMemCode nvarchar(12) COLLATE Chinese_Taiwan_Stroke_CI_AS null,
rMemName nvarchar(40) COLLATE Chinese_Taiwan_Stroke_CI_AS null,
rMemCName nvarchar(15) COLLATE Chinese_Taiwan_Stroke_CI_AS null,
rTransDate DateTime null,
rTransDateTime nvarchar(8),
rTransType nvarchar(2),
rTransTypeDesc nvarchar(20),
rTransPoints float null,
rOSPoint float null,
rExpiryDate DateTime null
)
DECLARE @sMemCodeIn nvarchar(8)
Declare @sStatus int
Declare @sOSPoints float
Declare @sRealOSPoints float
set @sStatus=0
Select @sMemCodeIn=MemCodeIn from dbo.mstMember Where memCode=@pMemCode
if isnull(@sMemCodeIn,'')='' begin
set @sStatus=-1
set @pStatusDesc='Member Code Not Found'
goto ERROREXIT
end
insert into #TempList (rSiteNo, rSiteName,rMemCode,rMemName,rMemCName, rTransDate,
rTransDateTime,
rTransType, rTransTypeDesc,rTransPoints,rOSPoint,rExpiryDate)
SELECT t.CompNo, CoName, @pMemCode, m.MemName, m.MemCName, TransDate,
substring(TransSortID, 9,2) +':'+substring(TransSortID, 9+2,2)+':'+substring(TransSortID, 9+2+2,2) as rTime,
tr.trCode, tr.trDesc, TransPoints, rOutPoints ,convert(datetime, ExpirePeriod,112) as rExpiryDate
FROM vwMemberHist t left join mstTranType tr on tr.trCode=t.TransType
left join mstMember m on m.MemCodeIn=t.MemCode
left join mCompany co on co.coCode=t.CompNo
WHERE t.MemCode=@sMemCodeIn and TransPoints > 0 and
rOutPoints>0 and
ExpirePeriod>=convert(nvarchar(8), getdate(),112)
set @sRealOSPoints = dbo.fnMemOsPoints2(@sMemCodeIn, convert(nvarchar(8), getdate(),112)+'2359')
if @sOSPoints <>@sRealOSPoints begin
set @sStatus=-2
set @pStatusDesc='Details O/S Points Not match O/S Points Summary'
goto ERROREXIT
end
else if @sOSPoints=0 begin
set @sStatus=-3
set @pStatusDesc='No Points Available'
goto ERROREXIT
end
Select rSiteNo, rSiteName,rMemCode,rMemName,rMemCName,rTransDate,
rTransDateTime, rTransType, rTransTypeDesc,rTransPoints,rOSPoint,rExpiryDate
from #TempList Order by rTransDate, rTransDateTime
set @sStatus=1
set @pStatusDesc='Successful'
ERROREXIT:
RETURN @sStatus
END下面是我MS SQL2005中掉用的存储过程DECLARE @return_value int,
@pStatusDesc nvarchar(50)
EXEC @return_value = [dbo].[spqMemOSPointsDtil]
@pMemCode = N'110106666666',
@pStatusDesc = @pStatusDesc OUTPUTSELECT @pStatusDesc as N'@pStatusDesc'
SELECT 'Return Value' = @return_value下面是我在JAVA中的掉用cstmt = conn.prepareCall("{call dbo.spqMemOSPointsDtil(?,?)}");
cstmt.setString(1,"110106666666");
cstmt.registerOutParameter(2,java.sql.Types.INTEGER);
resSet = cstmt.executeQuery();
System.out.println(cstmt.getInt(2));
//报错com.microsoft.sqlserver.jdbc.SQLServerException: 没有为参数号 0 设置值。
set QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[spqMemOSPointsDtil]
(
@pMemCode nvarchar(12),
@pStatusDesc nvarchar(50) output
)
AS
BEGIN
SET NOCOUNT ON CREATE TABLE #TempList
(
rSiteNo int,
rSiteName nvarchar(30) COLLATE Chinese_Taiwan_Stroke_CI_AS null,
rMemCode nvarchar(12) COLLATE Chinese_Taiwan_Stroke_CI_AS null,
rMemName nvarchar(40) COLLATE Chinese_Taiwan_Stroke_CI_AS null,
rMemCName nvarchar(15) COLLATE Chinese_Taiwan_Stroke_CI_AS null,
rTransDate DateTime null,
rTransDateTime nvarchar(8),
rTransType nvarchar(2),
rTransTypeDesc nvarchar(20),
rTransPoints float null,
rOSPoint float null,
rExpiryDate DateTime null
)
DECLARE @sMemCodeIn nvarchar(8)
Declare @sStatus int
Declare @sOSPoints float
Declare @sRealOSPoints float
set @sStatus=0
Select @sMemCodeIn=MemCodeIn from dbo.mstMember Where memCode=@pMemCode
if isnull(@sMemCodeIn,'')='' begin
set @sStatus=-1
set @pStatusDesc='Member Code Not Found'
goto ERROREXIT
end
insert into #TempList (rSiteNo, rSiteName,rMemCode,rMemName,rMemCName, rTransDate,
rTransDateTime,
rTransType, rTransTypeDesc,rTransPoints,rOSPoint,rExpiryDate)
SELECT t.CompNo, CoName, @pMemCode, m.MemName, m.MemCName, TransDate,
substring(TransSortID, 9,2) +':'+substring(TransSortID, 9+2,2)+':'+substring(TransSortID, 9+2+2,2) as rTime,
tr.trCode, tr.trDesc, TransPoints, rOutPoints ,convert(datetime, ExpirePeriod,112) as rExpiryDate
FROM vwMemberHist t left join mstTranType tr on tr.trCode=t.TransType
left join mstMember m on m.MemCodeIn=t.MemCode
left join mCompany co on co.coCode=t.CompNo
WHERE t.MemCode=@sMemCodeIn and TransPoints > 0 and
rOutPoints>0 and
ExpirePeriod>=convert(nvarchar(8), getdate(),112)
set @sRealOSPoints = dbo.fnMemOsPoints2(@sMemCodeIn, convert(nvarchar(8), getdate(),112)+'2359')
if @sOSPoints <>@sRealOSPoints begin
set @sStatus=-2
set @pStatusDesc='Details O/S Points Not match O/S Points Summary'
goto ERROREXIT
end
else if @sOSPoints=0 begin
set @sStatus=-3
set @pStatusDesc='No Points Available'
goto ERROREXIT
end
Select rSiteNo, rSiteName,rMemCode,rMemName,rMemCName,rTransDate,
rTransDateTime, rTransType, rTransTypeDesc,rTransPoints,rOSPoint,rExpiryDate
from #TempList Order by rTransDate, rTransDateTime
set @sStatus=1
set @pStatusDesc='Successful'
ERROREXIT:
RETURN @sStatus
END下面是我MS SQL2005中掉用的存储过程DECLARE @return_value int,
@pStatusDesc nvarchar(50)
EXEC @return_value = [dbo].[spqMemOSPointsDtil]
@pMemCode = N'110106666666',
@pStatusDesc = @pStatusDesc OUTPUTSELECT @pStatusDesc as N'@pStatusDesc'
SELECT 'Return Value' = @return_value下面是我在JAVA中的掉用cstmt = conn.prepareCall("{call dbo.spqMemOSPointsDtil(?,?)}");
cstmt.setString(1,"110106666666");
cstmt.registerOutParameter(2,java.sql.Types.INTEGER);
resSet = cstmt.executeQuery();
System.out.println(cstmt.getInt(2));
//报错com.microsoft.sqlserver.jdbc.SQLServerException: 没有为参数号 0 设置值。
解决方案 »
- hibernate映射
- 请帮我解释EJB中mappedBy的含义
- java程序错误,求各位指点一下哪错了,谢谢
- ejb整合tomcat问题初级
- 初学者JBuilder9 EJB CMP问题,执行ejb组件问题
- 应用struts时,当表单提交后,出现以下错误是什么原因?
- javamail 折磨人?
- [50分求解]下面的XSLT将XML转换成HTML为什么不行?
- 做企业应用是用Weblogic、Websphere好还是用Appach+tomcat好请大虾们给一个意见!!!
- BeanUtils.setProperty(bean, key, value)怎么没起作用啊
- java中字符串剔除html标签(急求)
- java字符(字符串)转十六进制问题
cstmt = conn.prepareCall("{call dbo.spqMemOSPointsDtil(?,?)}");
cstmt.setString(1,"110106666666");
cstmt.registerOutParameter(2,java.sql.Types.INTEGER);
cstmt.execute();
int i=cstmt.getInt(2);
System.out.println(i);
看看 cstmt.getInt(2)有没有取到值
報錯還是//报错com.microsoft.sqlserver.jdbc.SQLServerException: 没有为参数号 0 设置值。
cstmt.registerOutParameter(2,java.sql.Types.INTEGER);改成cstmt.setString(1,"110106666666");
cstmt.setString(2,"");
cstmt.registerOutParameter(2,java.sql.Types.VARCHAR);试试~~
调试 看看 conn 有没有拿到一个 连接另外cstmt = conn.prepareCall("{call dbo.spqMemOSPointsDtil(?,?)}");改为:cstmt = conn.prepareCall("{call spqMemOSPointsDtil(?,?)}");
cstmt.setString(1,"110106666666");
cstmt.setString(2,"");
cstmt.registerOutParameter(2,java.sql.Types.VARCHAR);
//能拿到一個返回值了,但我有兩個返回值,請高手再指教!
我怎么拿到结果集呢?