请教VB执行带参数的存储过程问题？

mryouhao 2015-04-17 10:04:46

VB执行存储过程，查不出来任何数据，SQL中却可以，请教下我的VB语句有什么问题吗？
在SQL中执行都可以查询出数据：
execute [testProcedure]
'2014-04-1',
'2015-04-10',
'',
'',
'',
'',
'a1',
'a0',
1,
1
但是用VB语句执行却没有任何数据：
cm.ActiveConnection = cn
cm.CommandText = "testProcedure" '存储过程名
cm.CommandType = adCmdStoredProc

cm.Parameters.Append cm.CreateParameter("@SDate", adVarChar, adParamInput, 50, "2014-04-10")
cm.Parameters.Append cm.CreateParameter("@EDate", adVarChar, adParamInput, 50, "2015-04-10")
cm.Parameters.Append cm.CreateParameter("@plan_no", adVarChar, adParamInput, 50, "")
cm.Parameters.Append cm.CreateParameter("@SoNo", adVarChar, adParamInput, 20, "")
cm.Parameters.Append cm.CreateParameter("@MkName", adVarChar, adParamInput, 50, "")
cm.Parameters.Append cm.CreateParameter("@PsName", adVarChar, adParamInput, 50, "")
cm.Parameters.Append cm.CreateParameter("@StateSQL", adVarChar, adParamInput, 10, "a1")
cm.Parameters.Append cm.CreateParameter("@LkState", adVarChar, adParamInput, 10, "a0")
cm.Parameters.Append cm.CreateParameter("@OutComp1PSOnline", adInteger, adParamInput, 10, 1)
cm.Parameters.Append cm.CreateParameter("@ifSum", adInteger, adParamInput, 10, 1)
Set rsTemp = cm.Execute()

存储过程如下：
ALTER PROCEDURE [dbo].[testProcedure]
@SDate datetime,
@EDate datetime,
@plan_no varchar(50),
@SoNo Varchar(20),
@MkName varchar(50),
@PsName varchar(50),
@StateSQL varchar(10) ,
@LkState varchar(10),
@OutComp1PSOnline bit,
@ifSum bit
AS
BEGIN
declare @Mk_Id varchar(50);
select @Mk_Id=Mk_id from bmmk where mk_name=@MkName
declare @Ps_Id varchar(50);
select @Ps_Id from bmps where ps_name=@PsName
SET NOCOUNT ON;
-- 传入ERP系统: mrt_RptPsList 参数
if @SDate is null or @SDate = 0 set @SDate = GETDATE()-3600
if @EDate is null or @EDate = 0 set @EDate = GETDATE() + 3600
if @plan_no is null or @plan_no='' set @plan_no ='%'
if @SoNo is null or @SoNo='' set @SoNo ='%'
--if @MkName is null or @MkName ='' set @MkName= '%'
if @Mk_Id='' or @Mk_Id is null
set @Mk_Id=''
ELSE
SET @Mk_ID=' mk_ID=''+@Mk_Id+'''
if @Ps_Id is null or @Ps_Id ='' set @Ps_Id ='%'
if @StateSQl is null or @StateSql ='' set @StateSQl =''
if @LkState is null or @LkState ='' set @LkState =''

------------------------------------------------------------
-- 创建保存ERP系统返回的临时表
------------------------------------------------------------
Create table #TempPSListA(
ps_sn int
, job_id int
, Plan_Id int
, Last_Flag int
, rTag int
, ToNull int
, Plan_Quan int
, FS_Quan decimal(18,2)
, MoveIn_Quan decimal(18,2)
, MoveOut_Quan decimal(18,2)
, Scrap_Quan decimal(18,2)
, PdC_Quan decimal(18,2)
, OnLine_Quan decimal(18,2)
, psR_Quan decimal(18,2)
, FsR_Quan decimal(18,2)
, MoveOutR_Quan decimal(18,2)
, PdcR_Quan decimal(18,2)
, ScrapR_Quan decimal(18,2)
, ROnLine_Quan decimal(18,2)
, StartDate DateTime
, EndDate DateTime
, sDelayDay DateTime
, eDelayDay decimal(18,2)
, PCompDay decimal(18,3)
, OCompDay decimal(18,4)
, OkRate decimal(18,5)
, CompRate decimal(18,6)
, Ps_Name varchar(20)
, ps_ID varchar(4)
, Plan_No varchar(20)
, Plan_Date datetime
,[State] int
, lkState int
, Job_No varchar(180)
, PStartDate datetime
, PEndDate datetime
, PS_URate decimal(18,2)
, Sp_ID int
, Sp_no varchar(50)
, Sp_Name varchar(180)
, sp_EName varchar(180)
, Sp_spec varchar(180)
, Unit_Id varchar(20)
, Unit_Name varchar(20)
, mk_ID varchar(12)
, mk_Name varchar(50)
,Spcf_Name varchar(50)
, SPCF_ID int
, LKDate datetime
, sSo_Nos varchar(50)
, sCP_Nos varchar(50)
)

insert into #TempPSListA
execute new_fujidata.dbo.mrp_rptpslist
@sDate,
@Edate,
@Plan_no,
@SoNo,
@PsName,
@Ps_Id,
@StateSql,
@LkState,
@OutComp1PSOnline

--if ifSum=0 --明细显示
select sp_no,sp_name,sp_spec,ps_sn,ps_name,sum(online_quan),sum(plan_quan),sum(fs_quan),sum(movein_quan),sum(moveout_quan),
sum(scrap_quan),sum(pdc_quan),sum(ronline_quan),sum(fsr_quan),sum(moveoutr_quan),sum(psr_quan),sum(scrapr_quan),sum(pdcr_quan)
from #TempPSListA group by sp_no,sp_name,sp_spec,ps_sn,ps_name
END

...全文