vc++中，ado执行sql server存储过程

_variant_t RecordsAffected; string szSql = (string)"P_KS_ADDUSER '" + m_szName+ "','" + m_szPasswd +"';"; try { _CommandPtr ptrCmd; ptrCmd.CreateInstance(__uuidof(Command)); ptrCmd->ActiveConnection = pContext->GetConnection(); VARIANT vtRoyalty; vtRoyalty = COleVariant((long)0); ptrCmd->Parameters->Append( ptrCmd->CreateParameter("Return", adInteger, adParamReturnValue,sizeof(int),vtRoyalty)); vtRoyalty = COleVariant(m_szName.c_str()); ptrCmd->Parameters->Append( ptrCmd->CreateParameter("Name", adChar, adParamInput,m_szName.size(),vtRoyalty)); vtRoyalty = COleVariant(m_szPasswd.c_str()); ptrCmd->Parameters->Append( ptrCmd->CreateParameter("Pass", adChar, adParamInput,m_szPasswd.size(),vtRoyalty)); ptrCmd->CommandText = "P_KS_ADDUSER"; ptrCmd->CommandType = adCmdStoredProc; ptrCmd->Execute(NULL,NULL,adCmdStoredProc); cout<<"添加用户成功，id="<<(unsigned int)ptrCmd->Parameters->GetItem("Return")->GetValue()<GetParameters()->GetItem(0)<GetNetworkSender()->SendPack(); } catch(_com_error e) { cout<<"增加用户的时候发生异常:"<<(LPCSTR)e.Description()<Open(strCnn,"","",NULL); pConnection->CursorLocation = adUseClient; //Open Command Object with one Parameter TESTHR(pcmdByRoyalty.CreateInstance(__uuidof(Command))); pcmdByRoyalty->CommandText = "byroyalty"; pcmdByRoyalty->CommandType = adCmdStoredProc; //Define Integer/variant. vtRoyalty.vt = VT_I2; vtRoyalty.iVal = intRoyalty; pprmByRoyalty = pcmdByRoyalty->CreateParameter( "percentage",adInteger,adParamInput,sizeof(int),vtRoyalty); pcmdByRoyalty->Parameters->Append(pprmByRoyalty); pprmByRoyalty->Value = vtRoyalty; //Create Recordset by executing the command pcmdByRoyalty->ActiveConnection = pConnection; pRstByRoyalty = pcmdByRoyalty->Execute( NULL,NULL,adCmdStoredProc); //执行结果的处理，省略... pRstByRoyalty->Close(); pRstAuthors->Close(); pConnection->Close(); } catch(_com_error &e) { //意外处理... } } 必须指定执行SQL语句的CommandType是adCmdStoredProc，存储过程有参数就必须CreateParameter生成参数，这样调用存储过程就成了很麻烦的一件事情，必须针对不同的存储过程生成不同的参数。一个两个还可以接受，如果项目的业务逻辑比较复杂，需要依赖大量的存储过程，并且项目的需求或功能可能要经常变动或扩展，那就成了一个噩梦了。有没有通用的方法呢？当然有，只需要通用的查询就可以实现。 _ConnectionPtr pConnection = NULL; m_Conn.CreateInstance(__uuidof(Connection)); m_Conn->ConnectionString = "Provider=sqloledb;Data Source=srv;"Initial Catalog=Pubs;User Id=sa;Password=;"; m_Conn->Open("","","",NULL); _variant_t m_param; _bstr_t m_bstr; int index = 0,nFieldCount; _RecordsetPtr m_Rs; m_Rs.CreateInstance(__uuidof(Recordset)); m_Rs->Open("select * from titles",(IDispatch*)(m_Conn->m_Conn),adOpenStatic, adLockReadOnly,adCmdText); nFieldCount = m_Rs->Fields->Count; while(!m_Rs->EndOfFile) { for(index=0;index < nFieldCount;index ++) { m_param.Clear(); m_param = (short)index; m_param = m_Rs->Fields->GetItem(m_param); if(m_param.vt != VT_NULL) m_bstr = m_param; else m_bstr = ""; printf("%s/n",(char*)m_bstr); } m_Rs->MoveNext(); } m_Rs->Close(); m_Conn->Close(); 以上一段就是普通的执行查询SQL语句的代码，简单起见，去掉了所有防护和判断代码，对于MS SQLServer，如果是执行返回结果集的存储过程，比如Pubs的byroyalty，只需要把上面代码中的"select * from titles"替换成"byroyalty 100"就可以了，即"过程名 参数1,参数2,..."形式。 这样一来，不需要生成参数，只需要改变SQL语句，就实现了MS SQLServer存储过程的调用，可以通用。但是对于返回参数的存储过程，这段代码就不行了，不过还是可以通过SQL语句解决： “declare @q int exec checkpwd 'user','pwd',@ret = @q OUTPUT select @q” checkpwd是一个存储过程，两个输入参数，用户名和密码，第三个是输出参数@ret，返回用户验证的结果。通过执行上面的SQL语句，把输出参数作为结果集返回，就可以适应上面的VC代码了。返回值的存储过程也是一样，执行“declare @q int,@return int exec @return = checkpwd 'user','pwd',@ret = @q OUTPUT select @q,@return”就可以了。 这里讲的是SQLServer的存储过程调用，这段代码同样可以调用ORACLE的存储过程，不过需要对SQL语句作出一些调整。 调用sql server 存储过程方法1如下： strSQL1="EXEC proc_getdianmingsonnode '"+AuthorName+"','"+FileName+"','"+DateTime+"','"+NQANum+"'"; conn.pCon->Execute(strSQL,&RecordsAffected,adCmdText); 其中AuthorName,FileName等是CString类的变量调用存储过程(有外传参数)方法2如下： _variant_t vFileName,vSonFileName,vTmp; CString FileName,SonFileName; FieldPtr pfldFileName; _ParameterPtr parm,parm1; conn.CreateConnection(); conn.pRs->MoveFirst(); pfldFileName = conn.pRs->Fields->GetItem("FileName"); ///向存储过程传入参数------- parm=conn.pCmd->CreateParameter("input",adVarChar,adParamInput,60,vFileName); conn.pCmd->Parameters->Append(parm); parm1=conn.pCmd->CreateParameter("output",adVarChar,adParamOutput,1024,vTmp); conn.pCmd->Parameters->Append(parm1); /执行存储过程-------------- pRs=conn.pCmd->Execute(NULL,NULL,-1); /得到存储过程的参数-------------------- vSonFileName=conn.pCmd->Parameters->GetItem(long(1))->GetValue(); 如果在循环中使用一定要 删除所传的参数，以以便下一条记录传参数 conn.pCmd->Parameters->Delete(parm->Name); conn.pCmd->Parameters->Delete(parm1->Name); HRESULT hr; VARIANT vNull; _ConnectionPtr pCnn; _CommandPtr pCmd; _ParameterPtr pParam1, pParam2; hr = pCnn.CreateInstance("ADODB.Connection"); if (SUCCEEDED(hr)) { hr = pCnn->Open("Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=CW111;Initial Catalog=CW111", "", "", adModeUnknown); if (SUCCEEDED(hr)) { hr = pCmd.CreateInstance("ADODB.Command"); if (SUCCEEDED(hr)) { VariantInit(&vNull); vNull.vt = VT_ERROR; vNull.scode = DISP_E_PARAMNOTFOUND; pCmd->ActiveConnection = pCnn; pCmd->CommandText = "procGetBooks"; pCmd->CommandType = adCmdStoredProc; pParam1 = pCmd->CreateParameter("@BookMark", adChar, adParamInput, 32, "CR-006"); pParam2 = pCmd->CreateParameter("@Ret", adChar, adParamOutput, 32); pCmd->Parameters->Append(pParam1); pCmd->Parameters->Append(pParam2); pCmd->Execute(&vNull, &vNull, adCmdStoredProc); AfxMessageBox ((char *)_bstr_t(pParam2->Value)); } pCnn->Close(); } } pCnn.Release(); 有参无返回存储过程 OleInitialize(NULL); _ConnectionPtr _pConn(__uuidof(Connection)); _variant_t _vRes; _pConn->Open("Provider=sqloledb; Server=192.168.1.155; Initial Catalog=SMSMessage; User ID=sa; Password=tendency",(BSTR) NULL, (BSTR) NULL, -1); CString strSQL; strSQL.Format("EXEC SMSMessage_Insert '4001','1','2001','2002145'"); _pConn->Execute((_bstr_t)strSQL,&_vRes,adCmdText); if(_pConn->GetState() == adStateOpen) _pConn->Close(); if(_pConn != NULL) _pConn.Release(); OleUninitialize(); 无参无返回存储过程 OleInitialize(NULL); _ConnectionPtr _pConn(__uuidof(Connection)); _variant_t _vRes; _pConn->Open("Provider=sqloledb; Server=192.168.1.155; Initial Catalog=SMSMessage; User ID=sa; Password=tendency",(BSTR) NULL, (BSTR) NULL, -1); _pConn->Execute("SMSMessage_Insert",&_vRes,adCmdStoredProc); if(_pConn->GetState() == adStateOpen) _pConn->Close(); if(_pConn != NULL) _pConn.Release(); OleUninitialize(); //有参有返回值的存储过程 _CommandPtr pIDCommand; pIDCommand.CreateInstance(__uuidof(Command)); pIDCommand->ActiveConnection = m_pConnection;//连接字符串 pIDCommand->CommandText = "pr_AddNew";//存储过程名 pIDCommand->CommandType = adCmdStoredProc;//标记该操作为存储过程 _ParameterPtr m_pParam; m_pParam = pIDCommand->CreateParameter("@reid",adInteger,adParamInputOutput,sizeof(int),0);//添加参数该参数为一个inputoutput类型的int参数 pIDCommand->Parameters->Append(m_pParam); m_pParam = pIDCommand->CreateParameter("@ReallyName",adVarChar,adParamInputOutput,128,ReallyName); //添加参数该参数为一个inputoutput类型的VarChar(128)参数 pIDCommand->Parameters->Append(m_pParam); m_pParam = pIDCommand->CreateParameter("@nodeid",adInteger,adParamInput,sizeof(int),root);// 该参数为input类型的int参数，参数的值在root中 pIDCommand->Parameters->Append(m_pParam); m_pParam = pIDCommand->CreateParameter("@ShowName",adChar,adParamInput,ShowName.GetLength(),ShowName.GetBuffer(0)); //数为input类型的char,参数的值在,ShowName.GetBuffer(0)中，该用法要回收 ShowName.ReleaseBuffer();//回收 pIDCommand->Parameters->Append(m_pParam); pIDCommand->Execute(NULL,NULL,adCmdStoredProc);//执行存储过程 Id = pIDCommand->Parameters->GetItem((long)0)->Value;//根据参数在参数表中的位置取值，当前参数是第一个，所以值为0 CString _reallyname= pIDCommand->Parameters->GetItem((long)1)->Value; //pIDCommand.Detach();//使用完毕后释放资源 pIDCommand.Release();//使用完毕后释放资源