The ability to call stored procedures has been added as a query and process Action. You will see this in the context help as EXEC_SP. This is a short list of things I have discovered about using them.
1.)If you have a Stored Procedure that will populate a table with data when called, then you need to make sure that the table is persisted as an existing external database table.
2.)If you have a Stored Procedure that will create a result set that you will display in a Query, then you must make sure that the last statement in Stored Procedure is SELECT ID,col1,col2,col3 FROM business_space_tablename
3.)When writing stored procedures you must you the actual table names (not the Business Object name).
4.)That the SELECT in a QUERY stored procedure requires the ID of the business object to be included in the SELECT statement in order to get it to work (makes sense, you need it to have the row in context).
5.)You can use a Stored Procedure to return a result set that is created from a UNION statement within the procedure
6.)When you have a process that uses the business object in context, and that context is being passed as an in parameter, you must fully quality the object and attribute
7.)You can pass parameters into and out of stored procedures. The syntax is in the help files.
8.)Lastly, read the help files on the EXEC_SP action – there are some good examples.
If anyone is interested then I will put up a sample bsv(s), with my stored procedures in the next couple of days. Please note that the procs will be for SQL Server or SQL Server Express (but you could probably run these on any other SQL92 compatible databases)
