[dba-SQLServer] MySQL not MS SQL question

Delimiter $$
Create or replace procedure ShowDistrict(DistID int)
Begin
Select * from tblDistricts where DistrictPK = DistID;
End$$

Then:

You would put: Call ShowDistrict(1)
as the SQL of your passthrough query


Since you can't stipulate parameters for a pass-through query,   You will have to rewrite the 
querydef before calling.the procedure to pull the correct record..

Assume that your have bound your form to the recordsource "qryShowDistrict" and that on 
your display form, you have an unbound textbox txtDistrictID and a button in addition to the 
"bound" fields.   You would do this:

Private Sub CmdShowDistrict_Click()
Dim qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("qryShowDistrict")
qd.SQL = "CALL SHowDistrict(" & txtDistrictID & ")"
Me.Requery
End Sub



On 25 Feb 2013 at 19:05, Arthur Fuller wrote:

> Ah! Thanks so much! I hope to be off to the races now. I had no idea you
> could use CALL in a pass-through query.
> 
> This begs a question. Assuming the SP accepts parameters, how to pass them
> in the CALL statement? just type them in after the SP name?
> 
> Thanks,
> A.
> 
> 
> On Mon, Feb 25, 2013 at 5:58 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote:
> 
> > You use CALL, not SELECT ....
> >
> > Here's a simple example:
> >
> > In MySQL, I run the following SQL:
> >
> > CREATE PROCEDURE ShowDistricts()
> > BEGIN
> > SELECT * FROM tblDistricts
> > END;
> >
> >
> > In Access I create a Passthrough query:
> >
> > In SQL view I just put: CALL ShowDistricts()
> > and for the ODBC Connect string, I either create a suitable COnnection
> > string or point to an
> > existing DSN for the MySQL database.
> >
> > I save the query.   I then create a new form and set its RecordSource to
> > the name of that
> > query.
> >
> >
> >
> >
> > On 25 Feb 2013 at 17:41, Arthur Fuller wrote:
> >
> > > I'm trying to stick with you on this, Stuart, but I don't understand what
> > > you mean by "put the stored procedure in a pass-thru query. If the SP
> > says
> > > "SELECT * FROM someTables" then how do I "put it in"? SELECT FROM
> > procName?
> > > That doesn't make sense. Can you please explain?
> > >
> > > TIA,
> > > Arthur
> > >
> > >
> > > On Mon, Feb 25, 2013 at 5:35 PM, Stuart McLachlan <
> > stuart at lexacorp.com.pg>wrote:
> > >
> > > > You can "Bind" the recordset from a stored procedure by putting that
> > > > procedure in a
> > > > pass-trhough query and "binding" to the query
> > > >
> > > > Note the quotes around "bind".  Recordset returned by  SPs are
> > snapshots,
> > > > there is no
> > > > persistent connection.   So you only view/filter/sort the records.  You
> > > > can't create/edit/delete
> > > > any.
> > > >
> > > > --
> > > > Stuart
> > > >
> > > >
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> > >
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> 
> 
> -- 
> Arthur
> Cell: 647.710.1314
> 
> Prediction is difficult, especially of the future.
>   -- Niels Bohr
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>