IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetMinMaxDate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[spGetMinMaxDate]( @sDate varchar(10) output, @eDate varchar(10) output)
as
declare @maxDate datetime
declare @varDate varchar(10)
set @varDate = convert(varchar(10), GETDATE(), 111)
set @maxDate = dateadd( day, -1, cast(@varDate as datetime) )
select @sDate = convert(varchar(10), min([記録日時]), 111),
@eDate = convert(varchar(10), max([記録日時]), 111)
from [F10JNLDB].[dbo].[AAA]
'
END
数据库已经连接上了,是通过ATL连接的,就是不知道怎样操作存储过程????请各位高手帮帮忙,谢谢。
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[spGetMinMaxDate]( @sDate varchar(10) output, @eDate varchar(10) output)
as
declare @maxDate datetime
declare @varDate varchar(10)
set @varDate = convert(varchar(10), GETDATE(), 111)
set @maxDate = dateadd( day, -1, cast(@varDate as datetime) )
select @sDate = convert(varchar(10), min([記録日時]), 111),
@eDate = convert(varchar(10), max([記録日時]), 111)
from [F10JNLDB].[dbo].[AAA]
'
END
数据库已经连接上了,是通过ATL连接的,就是不知道怎样操作存储过程????请各位高手帮帮忙,谢谢。
你说的直接在程序的connection 的command对象执行Execute就完了我不明白,我是要在VC6.0中通过OLEDB操作存储过程?
参考:
ParameterPtr para[3];
_CommandPtr pCmd;
pCmd.CreateInstance("ADODB.Command");
para[0].CreateInstance("ADODB.Parameter");
para[1].CreateInstance("ADODB.Parameter");
para[2].CreateInstance("ADODB.Parameter");
pCmd->ActiveConnection=m_pConn;
pCmd->CommandText="存储过程名"
para[0]=pCmd->CreateParameter("", adBSTR,adParamInput, sizeof(char[50]),vVar); //字符串型输入参数
pCmd->Parameters->Append(para[0]);
para[1]=pCmd->CreateParameter("", adInteger,adParamInput, sizeof(int),olevariantVar); //整型输入参数
pCmd->Parameters->Append(para[1]);
para[2]=pCmd->CreateParameter("", adBSTR,adParamOutput, sizeof(char[50]),""); //字符串型输出参数
pCmd->Parameters->Append(para[2]);
pCmd->Execute( NULL, NULL, adCmdStoredProc);
Microsoft® SQL Server™ 存储过程可包含整型返回代码及输出参数。返回代码和输出参数在服务器发送出的最后一个数据包中,因此直到行集完全释放后才能由应用程序使用。如果命令返回多个结果,输出参数数据在 IMultipleResults::GetResult 返回 DB_S_NORESULT 或当 IMultipleResults 接口完全释放(不论哪种情况先发生)时可用。处理返回代码和输出参数 1.使用 RPC 转义序列构造一个 SQL 语句。
2.调用 ICommandWithParameters::SetParameterInfo 方法以便向提供程序描述参数。在 PARAMBINDINFO 结构的数组中填充参数信息。
3.通过使用 DBBINDING 结构的数组创建一组绑定(每个参数标记有一个绑定)。
4.通过使用 IAccessor::CreateAccessor 方法为定义的参数创建存取程序。CreateAccessor 通过一组绑定创建存取程序。
5.填充 DBPARAMS 结构。
6.调用 Execute 命令(这里是指调用存储过程)。
7.处理行集并使用 IRowset::Release 方法释放该行集。
8.处理从存储过程收到的返回代码和输出参数值。
下例说明如何处理行集、返回代码和输出参数。不处理结果集。下面是由应用程序使用的示例存储过程。USE pubs
DROP PROCEDURE myProc
GOCREATE PROCEDURE myProc
@inparam int,
@outparam int OUTPUTAS
SELECT title, price
FROM titles WHERE royalty > @inparam
SELECT @outparam = 100IF (@outparam > 0)
RETURN 999
ELSE
RETURN 888
GO
void InitializeAndEstablishConnection();#define UNICODE
#define DBINITCONSTANTS
#define INITGUID
#include <windows.h>
#include <stdio.h>
#include <stddef.h>
#include <iostream.h>
#include <oledb.h>
#include <oledberr.h>
#include <SQLOLEDB.h>IDBInitialize* pIDBInitialize = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
ICommandText* pICommandText = NULL;
IRowset* pIRowset = NULL;
ICommandWithParameters* pICommandWithParams = NULL;
IAccessor* pIAccessor = NULL;
IDBProperties* pIDBProperties = NULL;
WCHAR* pStringsBuffer;
DBBINDING* pBindings;
const ULONG nInitProps = 4;
DBPROP InitProperties[nInitProps];
const ULONG nPropSet = 1;
DBPROPSET rgInitPropSet[nPropSet];
HRESULT hr;
HACCESSOR hAccessor;
const ULONG nParams = 3; //Number of parameters in the command
DBPARAMBINDINFO ParamBindInfo[nParams];
ULONG i;
ULONG cbColOffset = 0;ULONG ParamOrdinals[nParams];
LONG cNumRows = 0;
DBPARAMS Params;
/*
Declare an array of DBBINDING structures, one for each parameter
in the command.
*/
DBBINDING acDBBinding[nParams];
DBBINDSTATUS acDBBindStatus[nParams];//The following buffer is used to store parameter values.
typedef struct tagSPROCPARAMS
{
long lReturnValue;
long outParam;
long inParam;
} SPROCPARAMS;
//WCHAR* wCmdString = L"{? = call myProc(?,?)}";
WCHAR* wCmdString=L"{rpc myProc}";
SPROCPARAMS sprocparams = {0,0,14}; //All the initialization activities in a separate function.
InitializeAndEstablishConnection(); //Create a new activity from the data source object.
if(FAILED(pIDBInitialize->QueryInterface(
IID_IDBCreateSession,
(void**) &pIDBCreateSession)))
{
cout << "Failed to access IDBCreateSession interface.\n";
goto EXIT;
}
if(FAILED(pIDBCreateSession->CreateSession(
NULL,
IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand)))
{
cout << "pIDBCreateSession->CreateSession failed.\n";
goto EXIT;
} //Create a Command object.
if(FAILED(pIDBCreateCommand->CreateCommand(
NULL,
IID_ICommandText,
(IUnknown**) &pICommandText)))
{
cout << "Failed to access ICommand interface.\n";
goto EXIT;
}
//Set the command text.
if(FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString)))
{
cout << "Failed to set command text.\n";
goto EXIT;
} /*
Describe the command parameters (parameter name, provider
specific name of the parameter's data type, and so on.) in an array of
DBPARAMBINDINFO structures. This information is then used by
SetParameterInfo().
*/
ParamBindInfo[0].pwszDataSourceType = L"DBTYPE_I4";
ParamBindInfo[0].pwszName = L"ReturnVal"; //return value from sp
ParamBindInfo[0].ulParamSize = sizeof(long);
ParamBindInfo[0].dwFlags = DBPARAMFLAGS_ISOUTPUT;
ParamBindInfo[0].bPrecision = 11;
ParamBindInfo[0].bScale = 0;
ParamOrdinals[0] = 1;
ParamBindInfo[1].pwszDataSourceType = L"DBTYPE_I4";
ParamBindInfo[1].pwszName = L"@inparam";
ParamBindInfo[1].ulParamSize = sizeof(long);
ParamBindInfo[1].dwFlags = DBPARAMFLAGS_ISINPUT;
ParamBindInfo[1].bPrecision = 11;
ParamBindInfo[1].bScale = 0;
ParamOrdinals[1] = 2; ParamBindInfo[2].pwszDataSourceType = L"DBTYPE_I4";
ParamBindInfo[2].pwszName = L"@outparam";
ParamBindInfo[2].ulParamSize = sizeof(long);
ParamBindInfo[2].dwFlags = DBPARAMFLAGS_ISOUTPUT;
ParamBindInfo[2].bPrecision = 11;
ParamBindInfo[2].bScale = 0;
ParamOrdinals[2] = 3; //Set the parameters information.
if(FAILED(pICommandText->QueryInterface(
IID_ICommandWithParameters,
(void**)&pICommandWithParams)))
{
cout << "Failed to obtain ICommandWithParameters.\n";
goto EXIT;
}
if(FAILED(pICommandWithParams->SetParameterInfo(
nParams,
ParamOrdinals,
ParamBindInfo)))
{
cout << "Failed in setting parameter information.(SetParameterInfo)\n";
goto EXIT;
} //