[dba-SQLServer] MySQL not MS SQL question

So the MySQL would read:

DROP PROCEDURE mySP IF EXISTS$$
CREATE PROCEDURE mySP
AS
   etc
$$

A.


On Mon, Feb 25, 2013 at 7:56 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote:

> 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
> >
> >
>
>
> _______________________________________________
> 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