Sean,

Yes, your point about the original stored procedure brings up another idea.
Treat the input parameter as a named column, give them domain status, and
allow there to be defaults for them and also allow them to be optionally
included in the statement I shared. If an input parameter (column) isn't
included in the list then give it the default or null. Hasn't there been
some requests to give stored procedure parameters domain status?

Stretching things now I'm sure...

Regards,
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 6:13 PM
Subject: RE: [IB-Architect] Enhanced handling of EXECUTE PROCEDURE stateme
nt


>
> 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
> >
> >
> >
>
> ------------------------------------------------------------------------
> beMANY! has a new way to save big on your phone bill -- and keep on
> saving more each month: Our huge buying group gives you Long Distance
> rates which fall monthly, plus an extra $60 in FREE calls!
> http://click.egroups.com/1/3821/4/_/830676/_/961118046/
> ------------------------------------------------------------------------
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>