// *** Note: This example is specific to SQL Server ***
// for more information on processing return values from
// SQL Server stored procedures, see this documentation
// stored procedure being called in this example
#if 0
DROP PROCEDURE TestParm
-- Example procedure returns three things:
-- 1. A set of records from the select statement: "SELECT STRING_VALUE FROM DB_EXAMPLE"
-- After all records have been retrieved, output paramenters are returned:
-- 2. OutParm
-- 3. Return value for function
CREATE PROCEDURE TestParm @OutParm int OUTPUT AS
SELECT STRING_VALUE FROM db_example
SELECT @OutParm = 66
RETURN 99
DECLARE @RetVal INT
DECLARE @Param INT
-- Execute the procedure, which returns
-- the result set from the first SELECT.
EXEC @RetVal = TestParm @OutParm = @Param OUTPUT
-- Use the return code and output parameter.
PRINT 'The return value from the procedure was: ' +
CONVERT(CHAR(6), @RetVal)
PRINT 'The value of the output parameter in the procedure was: ' +
CONVERT(CHAR(6), @Param)
#endif
class TestParmBCA {
public:
void operator()(BoundIOs &cols, variant_row &row)
{
cols["STRING_VALUE"] == row._string();
cols[0] >> row._int();
cols[1] >> row._int();
cols.BindVariantRow(row);
}
};
// Read the contents of a table and print the resulting rows
void StoredProcReadTestParm() {
DBView<variant_row> view("{? = call TestParm(?)}",
TestParmBCA());
// NOTE: We need to construct r from the view itself since we
// don't know what fields the table will contain.
// We therefore make a call to the DataObj() function to have the
// table return us a template row with the correct number of fields
// and field types.
// We use this construction since we can't be guaranteed that the table
// is non-empty & we want to still display column names in this case.
variant_row s(view.GetDataObj());
// Print out the column names
vector<string> colNames = s.GetNames();
for (vector<string>::iterator name_it = colNames.begin(); name_it !=
colNames.end(); ++name_it)
cout << (*name_it) << " ";
cout << endl;
// Print out all rows and columns from our query
DBView<variant_row>::sql_iterator print_it = view;
// By default DTL uses server side cursors for SQL Server so that more than
// one iterator can be active at a time. This is set in the constructor
// for DBStmt.
// Here we require a client side cursor because our stored procedure returns
// multiple result sets. Therfore we clear out the setting to use server
// side cursors in which case SQL server will default to a client side cursor.
// For details on server side versus client side cursors
// see Rowsets and SQL Server Cursors
print_it.GetStmt().ClearStmtAttrs();
variant_row r = view.GetDataObj();
r[0] = 0;
r[1] = 0;
for (++print_it; print_it != view.end(); ++print_it)
{
r = *print_it;
for (size_t i = 0; i < r.size(); ++i)
cout << r[i] << " ";
cout << endl;
}
cout << endl;
cout << "After call to MoreResults(), "
"SQL-Server gives results in output parameters & return code." << endl;
print_it.MoreResults();
r = *print_it;
for (size_t i = 0; i < r.size(); ++i)
cout << r[i] << " ";
cout << endl;
}