For preference I tend to use stored procedures for data retrieval especially where there the SELECTs from 10 or more tables are too complex for SQL statements. All of our databases are in at least 3NF. By using procedures the results from SELECTs are placed into output parameters.

One of the tedious tasks which is prone to error is the specifying of parameters in the app code whereby the values of the above output parameters are used as input parameters in the ensuing SQL INSERT statements. All the reads and writes occur within one transaction and the writes are not normally to the same database.

I am looking to some way of automating this process. From the System Tables you can retrieve each Parameter Name and its location number within the Procedure Name together with its type. Now, owing to various casts and formats of the retrieved data for the receiver (eg. a client), we also need to retrieve each parameter's datatype.

The system tables tend to reveal that the datatype info is stored in the Blob which contains that procedure's text. Is it necessary to extract the datatype from the header, or is there a simpler method whereby the datatype is stored elsewhere? I have looked but I might not be seeing the wood for the trees.

Thanking you in advance.

Brian Gooch.