What extra procedure? (I guess I should have have also called my sample
SP APROCEDURE)

Wouldn't your original APROCEDURE have required a defined number of
parameters (therefore, not completely SQL to go).

In either case the SP has a specific task to perform.

You are proposing is to pass the SP a recordset. In my case, the SP
build it's own recordset.

I don't see the difference.

With my approach, however, it could be possible to pass not only the
source table and column names but also the table/column names for other
structure which the SP might need to access/update. This would open a
whole host of possibilities. In this way only only is the source
user-definable but also the "destinations".


Sean

> -----Original Message-----
> From: Jason Wharton [mailto:jwharton@...]
> Sent: Thursday, June 15, 2000 9:02 PM
> To: IB-Architect@egroups.com
> Subject: Re: [IB-Architect] Enhanced handling of EXECUTE PROCEDURE
> stateme nt
>
>
> That still requires an extra stored procedure. I want to
> avoid that. I want
> to just produce a dynamic SQL statement and be good to go. I
> think what you
> are suggesting here may not work like I would need it to.
> Seems there is
> still a need to have declared variables. What if I needed
> three or more of
> them?
>
> I can think of other cool things that your suggestions would
> allow. I think
> there is sufficient merit for both approaches here.
>
> Thanks for the comments.
>
> Jason Wharton
> CPS - Mesa AZ
> http://www.ibobjects.com
>
>
> ----- Original Message -----
> From: "Leyne, Sean" <InterbaseArchitecture@...>
> To: <IB-Architect@egroups.com>
> Sent: Thursday, June 15, 2000 5:49 PM
> Subject: RE: [IB-Architect] Enhanced handling of EXECUTE
> PROCEDURE stateme
> nt
>
>
> >
> > Jason,
> >
> > Wouldn't this situation also be handled by allowing for
> dynamic table
> > and columns name in SP's?
> >
> > A sample SP would read something like
> >
> > CREATE PROCEDURE SOME_TABLESP(
> > TableName CHAR(31),
> > ColumnName1 CHAR( 31),
> > ColumnName2 CHAR( 31),
> > WhereClause Text
> > )
> > AS
> > declare variable var1 whatever;
> > declare variable var2 whatever;
> > BEGIN
> > FOR
> > SELECT @ColumnName1 , @ColumnName2
> > FROM @TableName
> > @WhereClause
> > INTO Var1, Var2
> > DO
> > {Perform some operation}
> > END
> > END
> >
> > I think that this approach would be more "approachable"
> with respect to
> > implementation (there already have been requests for dynamic SPs) as
> > well as having some hope of cross-platform (ie. accross SQL Server)
> > support.
> >
> > Just my .02
> >
> >
> > Sean
> >
> > > -----Original Message-----
> > > From: Jason Wharton [mailto:jwharton@...]
> > > Sent: Thursday, June 15, 2000 8:30 PM
> > > To: IB-Architect@egroups.com
> > > Subject: [IB-Architect] Enhanced handling of EXECUTE
> > > PROCEDURE statement
> > >
> > >
> > > I would like to suggest an enhancement to the SQL
> > > capabilities of InterBase
> > > and wonder what the architectural feasibilities of them are.
> > >
> > > In the same way you can do this:
> > >
> > > INSERT INTO ATABLE ( <list of columns> )
> > > SELECT <list of columns>
> > > FROM ANOTHERTABLE
> > > WHERE <record selection criteria>
> > >
> > > I would like to be able to do this:
> > >
> > > EXECUTE PROCEDURE APROCEDURE ( <list of columns> )
> > > SELECT <list of columns>
> > > FROM ANOTHERTABLE
> > > WHERE <record selection criteria>
> > >
> > > What has to be done right now is to write an additional
> > > stored procedure
> > > with a FOR SELECT and then in it's loop call the EXECUTE PROCEDURE
> > > statement. This is really a pain to have to do and not only
> > > that you cannot
> > > dynamically adjust the where clause of the SELECT that
> determines the
> > > records filtered into the stored procedure because that part
> > > is actually in
> > > a stored procedure too.
> > >
> > > This seems like it should be a simple allowance but I wonder
> > > if there is
> > > anything more to it than meets the eye. I suspect it might
> > > have something to
> > > do with the EXECUTE PROCEDURE construct being able to return
> > > results whereas
> > > the INSERT statement does not.
> > >
> > > All comments and suggestions appreciated.
> > >
> > > Jason Wharton
> > > CPS - Mesa AZ
> > > http://www.ibobjects.com
>
>
>
> --------------------------------------------------------------
> ----------
> Missing old school friends? Find them here:
> http://click.egroups.com/1/5534/4/_/830676/_/961117451/
> --------------------------------------------------------------
> ----------
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>