An Open SQL Stored Procedure Example
This example shows how to invoke a stored procedure,
EmpInDept, using an
RWDBOSql object, fetch result sets and fetch output parameters.
CREATE PROCEDURE EmpInDept(@deptno INT, @empcount INT OUT)
AS
SELECT @empcount = COUNT(*) FROM emp WHERE emp.dept = @deptno
SELECT empno, ename FROM emp WHERE emp.dept = @deptno
SELECT MIN(sal), AVG(sal), MAX(sal) FROM emp WHERE emp.dept = @deptno
The stored procedure takes a department number as an input parameter and provides the count of employees in that department as an output parameter. It produces two result sets: the first returns a list of employee number and name for each employee in that department; the second result set returns a single row with three columns containing the minimum, average and maximum salary of employees in that department.
RWDBOSql openSql ("{CALL EmpInDept(?, ?) }", RWDBOSql::Procedure); //1
RWDBTBuffer<int> dept, empCount; //2
empCount.paramType(RWDBColumn::outParameter); //3
openSql << dept << empCount; //4
dept[0] = 2; //5
openSql.execute(cn); //6
const size_t NUMBER_OF_ROWS = 10;
RWDBTBuffer<int> empNums (NUMBER_OF_ROWS); //7
RWDBTBuffer<RWCString> empNames (NUMBER_OF_ROWS); //8
openSql[0] >> empNums >> empNames; //9
long count = 0;
while ((count = openSql.fetch().rowsFetched()) > 0 ) { //10
for (int row = 0; row < count; row++) {
std::cout << "Employee: " << empNums[row] << ", "
<< empNames[row] << std::endl; //11
}
}
RWDBTBuffer<RWDecimalPortable> minBuff, avgBuff, maxBuff; //12
openSql[1] >> minBuff >> avgBuff >> maxBuff; //13
openSql.fetch(); //14
std::cout << "\nMinimum Salary: " << minBuff[0]
<< "\nAverage Salary: " << avgBuff[0]
<< "\nMaximum Salary: " << maxBuff[0]
<< std::endl; //15
openSql.fetchReturnParams(); //16
std::cout << "Number of Employees in dept " << dept[0] << ": "
<< empCount[0] << std::endl; //17
