[dba-SQLServer] MySQL not MS SQL question

My bad.

> Create or replace procedure ShowDistrict(DistID int)

That doesn't work in MySQL.

Lose the "or replace".  If the SP already exists, you need to DROP it first.

-- 
Stuart

On 26 Feb 2013 at 10:46, Stuart McLachlan wrote:

> 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
> > 
> > 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>