学步园

try
{
ptrConn->CursorLocation =adUseClient;
_CommandPtr ptrCmd=NULL;
ptrCmd.CreateInstance(_uuidof(Command));
ptrCmd->ActiveConnection=ptrConn;
ptrCmd->CommandText=_bstr_t("QueryAddworkSalary");//过程名
ptrCmd->CommandType=adCmdStoredProc;

_ParameterPtr mp_var1,mp_var2;
mp_var1.CreateInstance(__uuidof(Parameter));
mp_var2.CreateInstance(__uuidof(Parameter));
_variant_t var1(str.GetBuffer());
mp_var1=ptrCmd->CreateParameter
(
_bstr_t("@Sno"),
adChar,
adParamInput,
10,
var1
);
ptrCmd->Parameters->Append(mp_var1); 

_variant_t var2;
mp_var2=ptrCmd->CreateParameter
(
_bstr_t("@AddWorkSal"),
adInteger,//这里如果改成adNumberic，会出现 [Microsoft][ODBC SQL Server Driver 无效的缩放值，应该是和存储过程精度不匹配
adParamOutput,
0,
var2
);
ptrCmd->Parameters->Append(mp_var2); 
_variant_t vNull;
vNull.vt=VT_ERROR;
vNull.scode=DISP_E_PARAMNOTFOUND;
ptrCmd->Execute(&vNull,&vNull,adCmdStoredProc);
_variant_t value1;
value1 = mp_var2->Value;
value=value1.intVal;
/*CString str((wchar_t*)(_bstr_t)(value1));
MessageBox(str)*/
}
catch(_com_error &e)
{
CString str;
CString strTemp;
str.Format(TEXT("Error:\n"));
strTemp.Format(TEXT("Code = %08lx\n"), e.Error());
str+="\n";
str+=strTemp;

strTemp.Format(TEXT("Meaning = %s\n"), e.ErrorMessage());
str+="\n";
str+=strTemp;

strTemp.Format(TEXT("Source = %s\n"), (wchar_t*) e.Source());
str+="\n";
str+=strTemp;

strTemp.Format(TEXT("Description = %s\n"), (wchar_t*) e.Description());
str+="\n";
str+=strTemp;
MessageBox(str);
return false;

}

存储过程如下:

create procedure QueryAddworkSalary --计算，输入一个员工工号，输出一个员工的工资总和
@Sno char(10),
@AddWorkSal int
as
 declare @level int
 declare @Ordhoursalary decimal(10,3)
 declare @Spechoursalary decimal(10,3)
 declare @Holidaysalary decimal(10,3)
 declare @Roridinaryhours decimal(5,2)
 declare @Rspecifialhours decimal(5,2)
 declare @Rholiday int

  set @level= (select Slevel from dbo.StaffInfo where Sno=@Sno)--获得该员工的等级
  set @Ordhoursalary =(select Ordhoursalary from dbo.TypeOfWork where Worklevel=@level) --获得该员工津贴信息
  set @Spechoursalary =(select Spechoursalary from dbo.TypeOfWork where Worklevel=@level) --获得该员工津贴信息
  set @Holidaysalary =(select Holidaysalary from dbo.TypeOfWork where Worklevel=@level) --获得该员工津贴信息
  
  set @Roridinaryhours = (select COUNT(Roridinaryhous) from dbo.Overtime where Sno =@sno);
  set @Rspecifialhours= (select COUNT(Rspecifialhours) from dbo.Overtime where Sno =@sno);
  set @Rholiday = (select COUNT(Rholiday) from dbo.Overtime where Sno =@sno);
  
  set @AddWorkSal =(select @Ordhoursalary*@Roridinaryhours +@Spechoursalary*@Rspecifialhours+@Holidaysalary*@Rholiday )
  GO