Creating a Stored Procedure

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::Long);

aParameter.paramType(RWDBColumn::inParameter);

params.appendColumn(aParameter);

aParameter.name("@newsales").type(RWDBValue::Long);

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);