Stored Procedures - v4.8 the EXEC_SP Action

[thom]

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)

[[email protected]]

Thom,

Hi, I am interested in seeing your sample BSV.

Thanks

Rob

[[email protected]]

Thom,

Hi, I am interested in seeing your sample BSV.

Thanks

Rob

[ACDC]

Hi Thom

Could you briefly describe a typical scenario when one would need to use such a feature

many thanks

[wings]

Hi Thom

Would love to see an example bsv as we are interested in using this functionality.

Thanks, wings

[bartthefish]

Hi Thom.

Would also really be interested in seeing your sample bsv and SP.

With regards to when this would be useful: I find that I'm starting to need to migrate some really complex queries and functions to our Aware App, and while the business rules are quite powerful, I can't seem to implement these queries within Aware. Which makes this EXEC_SP action a real winner.

I guess I join the queue of those waiting with bated breath for your sample Thom.

[bmeyns]

Any idea how the dynamic query-sorting (click on column-headers) could be fixed when using a stored procedure to retrieve the objects?
I've managed to call a stored procedure and have BO's returned in my query-grid , but the user-sorting doesn't seem to work out anymore in that case.
Should be awesome if I could fix this too.