Hi Randall.

I'd say you have at least two other alternatives:

1) Write things directly in your stored procedure:

DECLARE VARIABLE DOFULL SMALLINT;
BEGIN
DOFULL = 1;
IF (DOFULL = 1) then
FOR SELECT
DESCRIPTION,
COMPANYNAME
FROM PASWORD_VIEW_FULL
INTO
:DESCRIPTION,
:COMPANYNAME
DO BEGIN
SUSPEND;
END
ELSE
FOR SELECT
DESCRIPTION,
COMPANYNAME
FROM PASWORD_VIEW_LITE
INTO
:DESCRIPTION,
:COMPANYNAME
DO BEGIN
SUSPEND;
END
END

The drawback is that this is verbose, and quickly gets complicated if you have lots of conditions.

2) Replace your views with a single view with a where clause similar to:

WHERE <FieldName> = <condition> or CURRENT_USER = <whomever is to be given full access>

Replace CURRENT_USER with whatever should determine if the user gets full access. One of the drawbacks with this approach, is that no index can be used for <FieldName>, something that may make queries slow on largish tables. Though if you intend to have one general procedure and then apply WHERE conditions to its output fields, then you already suffer from something similar (well, I don't use Fb 2.0 yet, and ain't sure whether expression indexes can be used to create indexes for output fields of stored procedures - I would be delighted if they could, but doubt it is possible).

HTH,
Set
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of randallsell
Sent: 15. november 2007 08:35
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Dynamic name in a stored procedure

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> >

> There is no "correct SQL syntax" for passing object references as

arguments to stored procedures.

I was afraid of that.

>
> > I'm trying to get my stord procedure to return results from one of

2 views based > on a condiiton. SourceView should have one of two view
names.

>
> There is a way to do this. Study the documentation in the v.1.5

release notes for EXECUTE STATEMENT. It was implemented for
situations like this. Essentially, you pass the object name as a
VARCHAR(31) variable and construct your statement by concatenation at
run-time.

It seems to me, and hopefully this is a fair analogy, is that this is
like writing JavaScript code that creates other JavaScript code. Not
exactly a very pure or clean solution.

Let me get back to my end-goal to determine if I am taking the right
approach. My end goal is to have just one stored procedure that all
clients call. It will pass back different results based on who is
logged in (CURRENT_USER). In reality the only real difference is the
WHERE clause. For the FULL access - there is no where clause. For the
limited access I strip things down to a simple condition.

As your book points out, the limitations of using EXECUTE STATEMENT
seems to outstrip its usefulness. But if I don't use this approach, my
only other alternative (that I am aware of) would be to start writing
business logic on the client end.

cheers,
-randall