http://www.codeguru.com/mfc/comments/18449.shtml How to call SP with return values dynamically.--------------------------------------------------------------------------------Please find some code that enables you to call a stored procedure dynamically without having to use a CRecordset derivative: // An example of calling a stored procedure: // ? = GetDocketRefFromID(?) // Where parm1 is a string (max len 255) // parm2 is an integer.#define MAXBUFLEN 255 SQLHSTMT hstmt1 = SQL_NULL_HSTMT; RETCODE nRetCode; SQLCHAR sParm1[MAXBUFLEN]; SWORD sParm2=DocketID; // Set from our docketID SDWORD cbParm1=SQL_NTS, cbParm2=SQL_NTS;// Allocate statement handle from the connection with // the database // NB GenericConfigDatabase returns ptr to our CDatabase // object... AFX_SQL_SYNC(SQLAllocHandle(SQL_HANDLE_STMT, GenericConfigDatabase()->m_hdbc, &hstmt1));
// Bind the return code to variable sParm1 AFX_SQL_SYNC(SQLBindParameter(hstmt1,1,SQL_PARAM_OUTPUT,SQL_C_CHAR, SQL_CHAR,MAXBUFLEN,0,&sParm1,MAXBUFLEN,&cbParm1));// Bind the input value to variable sParm1 AFX_SQL_SYNC(SQLBindParameter(hstmt1,2,SQL_PARAM_INPUT,SQL_C_SSHORT, SQL_INTEGER,0,0,&sParm2,0,&cbParm2));// Execute the command. AFX_SQL_SYNC(SQLExecDirect(hstmt1, (SQLCHAR*)"{? = call GetDocketRefFromID(?)}", SQL_NTS));// Clear any result sets generated. while ( ( nRetCode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA ) ; /* Clean up. */ SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
How to call SP with return values dynamically.--------------------------------------------------------------------------------Please find some code that enables you to call a stored procedure dynamically without having to use a CRecordset derivative:
// An example of calling a stored procedure:
// ? = GetDocketRefFromID(?)
// Where parm1 is a string (max len 255)
// parm2 is an integer.#define MAXBUFLEN 255
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
RETCODE nRetCode;
SQLCHAR sParm1[MAXBUFLEN];
SWORD sParm2=DocketID; // Set from our docketID
SDWORD cbParm1=SQL_NTS, cbParm2=SQL_NTS;// Allocate statement handle from the connection with
// the database
// NB GenericConfigDatabase returns ptr to our CDatabase
// object...
AFX_SQL_SYNC(SQLAllocHandle(SQL_HANDLE_STMT, GenericConfigDatabase()->m_hdbc, &hstmt1));
// Bind the return code to variable sParm1
AFX_SQL_SYNC(SQLBindParameter(hstmt1,1,SQL_PARAM_OUTPUT,SQL_C_CHAR,
SQL_CHAR,MAXBUFLEN,0,&sParm1,MAXBUFLEN,&cbParm1));// Bind the input value to variable sParm1
AFX_SQL_SYNC(SQLBindParameter(hstmt1,2,SQL_PARAM_INPUT,SQL_C_SSHORT,
SQL_INTEGER,0,0,&sParm2,0,&cbParm2));// Execute the command.
AFX_SQL_SYNC(SQLExecDirect(hstmt1, (SQLCHAR*)"{? = call GetDocketRefFromID(?)}", SQL_NTS));// Clear any result sets generated.
while ( ( nRetCode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )
; /* Clean up. */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);