Creating Stored Procedures
Stored procedures can be created by calling the
createProcedure() method of
RWDBDatabase. The DB Interface Module will create the parameter list from a schema you supply, while the body of the stored procedure is supplied by the application. Here is a simple example, which looks up a royalty percentage and returns the royalty schedule based on title and sales level:
RWCString body =
"declare @total int \n"
"select @total = titles.total_sales \n"
" from titles where title_id = @title_id \n"
"if ( @total > 0 ) \n"
" begin \n"
" select @total = @total + @newsales \n"
" select @pc = royalty from roysched \n"
" where @total >= roysched.lowrange and \n"
" @total < roysched.highrange \n"
" and roysched.title_id = @title_id \n"
" select royalty,lowrange,highrange from roysched \n"
" where roysched.title_id = @title_id \n"
" return 1 \n"
" end \n"
"else \n"
" return 99 ";
RWDBSchema params;
RWDBColumn aParameter;
aParameter.name("@title_id").type(RWDBValue::Int);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);
aParameter.name("@newsales").type(RWDBValue::Int);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);
aParameter.name("@pc").type(RWDBValue::Float);
aParameter.paramType(RWDBColumn::outParameter);
params.appendColumn(aParameter);
aDb.createProcedure("royalty", body, params);
