At 05:45 PM 27-12-01 +0500, you wrote:

>For e.g. I am writing a inventory program. My parts master has 120 fields.
>Based on the users/department and authorisations.
>I build up an SQL statement which will return only the appropriate fields.
>
>Authorisations etc. are user configurable.
>With this scheme the front end just displays all the fields that are returned.

Right, that feature (EXECUTE "any string") is SQL Server-specific. Sorry, there won't be a simple way to duplicate that exactly in InterBase. The structure that IB returns to the client from a SP is predefined and precompiled.

>So how do we do this in IB.

An approach would be to define what's known as a "selectable stored procedure". (See the FOR...SELECT...INTO...DO psql construct) that potentially returns output for all columns. You could dynamically construct your SQL with a column list returned to your client by a parameterised query on your Authorisations table (just guessing that this table somehow provides this list), i.e. you don't have to ask the selectable procedure to return all of its output columns.

Another way to look at this would be to observe that it was feasible for your database designer to put all 120 columns into the same Parts table, regardless of their visiblility to queries, just because SQL Server would allow you to define SP's containing EXECUTE "any string". Moving to InterBase, the rules of application integrity are much more rigorous - one simply isn't invited (so far, anyway) to do "ad hoc" stuff in client-initiated server-based processing that can potentially break something. (Never mind that, with a rogue UDF, we can break just about anything, just as one can do with extended procedures in SQLServer. :)) )

This strictness (UDFs aside) encourages us to granulate relational structures so that data which should be hidden from some users are protected by permissions on database objects (specifically, tables, views and stored procedures) rather than depending on application code to decide what's visible to whom.

Sorry I can't be much more help...

regards,
Helen