Defining Stored Procedures

Before calling a stored procedure, you must first define it, using the DEFINE_COMMAND macro. When you define the command, denote parameters with a question mark (?) as the parameter marker:

В	Copy Code
DEFINE_COMMAND(CMySProcAccessor, _T("{INSERT {name, phone} into shippers (?,?)}")

Note that the syntax (the use of braces and so on) used in the code examples in this topic is specific to SQLВ Server. The syntax that you use in your stored procedures might vary according to the provider you use.

Next, in the parameter map, specify the parameters that you used in the command, listing the parameters in the order that they occur in the command:

В	Copy Code
BEGIN_PARAM_MAP(CMySProcAccessor) SET_PARAM_TYPE(DBPARAMIO_INPUT) COLUMN_ENTRY(1, m_Name) // name corresponds to first '?' param SET_PARAM_TYPE(DBPARAMIO_INPUT) COLUMN_ENTRY(2, m_Phone) // phone corresponds to second '?' param END_PARAM_MAP()

Copy Code

BEGIN_PARAM_MAP(CMySProcAccessor)
   SET_PARAM_TYPE(DBPARAMIO_INPUT)
   COLUMN_ENTRY(1, m_Name)   // name corresponds to first '?' param
   SET_PARAM_TYPE(DBPARAMIO_INPUT)
   COLUMN_ENTRY(2, m_Phone)  // phone corresponds to second '?' param
END_PARAM_MAP()

The previous example defines a stored procedure as it goes. Typically, for efficient reuse of code, a database contains a set of predefined stored procedures with names such as "Sales by Year" or "dt_adduserobject." You can view their definitions using SQLВ Server Enterprise Manager. You call them as follows (the placement of the '?' parameters depends on the stored procedure's interface):

В	Copy Code
DEFINE_COMMAND(CMySProcAccessor, _T("{CALL \"Sales by Year\" (?,?) }") DEFINE_COMMAND(CMySProcAccessor, _T("{CALL dbo.dt_adduserobject (?,?) }")

Next, declare the command class:

В	Copy Code
class CMySProc : public CCommand<CAccessor<CMySProcAccessor> >

Finally, call the stored procedure in OpenRowset as follows:

В	Copy Code
CSession m_session; HRESULT OpenRowset() { return CCommand<CAccessor<CMySProcAccessor> >::Open(m_session); }

Also note that you can define a stored procedure using the database attribute db_command as follows:

В	Copy Code
db_command("{ ? = CALL dbo.dt_adduserobject }")

See Also

Concepts