List:General Discussion« Previous MessageNext Message »
From:Mark Leith Date:August 2 2005 2:55pm
Subject:RE: STORED PROCEDURE
View as plain text  
> -----Original Message-----
> From: Scott Hamm [mailto:linuxgold@stripped]
> Sent: 02 August 2005 15:38
> To: mark@stripped
> Cc: Mysql
> Subject: Re: STORED PROCEDURE
>
> On 8/2/05, Mark Leith <mark@stripped> wrote:
> >
> > CREATE PROCEDURE title()
> > BEGIN
> > DECLARE title VARCHAR(255);
> > SET title = '%Unconditional%';
> > SELECT title;
> > END;
> > //
> >
> > mysql> CALL title()//
> > +-----------------+
> > | title |
> > +-----------------+
> > | %Unconditional% |
> > +-----------------+
> > 1 row in set (0.01 sec)
> >
> > HTH
> >
> > Mark
> >
> > Mark Leith
> > Cool-Tools UK Limited
> > http://www.cool-tools.co.uk
> >
> > > -----Original Message-----
> > > From: Scott Hamm [mailto:linuxgold@stripped]
> > > Sent: 02 August 2005 15:12
> > > To: 'Mysql '
> > > Subject: STORED PROCEDURE
> > >
> > > I'm used with MS SQL and could not understand MySQL's
> document. I'm
> > > trying to create something like the simple example
> > >
> > > DECLARE @title varchar(255)
> > > SET @title='%Unconditional%'
> > > SELECT @title;
> > >
> > > How do I get around to it in MySQL?
> > >
> > > --
> > > Power to people, Linux is here.
> > >
> > > --
> > > No virus found in this incoming message.
> > > Checked by AVG Anti-Virus.
> > > Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
> > > 01/08/2005
> > >
> > >
> >
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Anti-Virus.
> > Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
> > 01/08/2005
> >
> >
> >
> Something similiar to
> CREATE PROCEDURE Select_title()
> BEGIN
> DECLARE u_title varchar(255)
> SET u_title='%Unconditional%'
> SELECT
> T.Title,
> B.BAND_Name,
> C.Type,
> T.Track
> FROM Title T
> LEFT JOIN Bands B ON B.BandID=T.B_ID
> LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
> WHERE Title LIKE u_title
> ORDER BY Title;
> END;
>
> --
> Power to people, Linux is here.
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
> 01/08/2005
> 
>

Yea, that will work - just modify the example I gave above to yours above..
What the hell, I'll even write it:

DELIMITER //

CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255);
SET u_title='%Unconditional%';
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//

Seems a bit of a strange use for a stored procedure though - I would have
thought something like this would be of more use:

CREATE PROCEDURE Select_title( u_title VARCHAR(255) )
BEGIN
CASE
  WHEN u_title = '' THEN SET u_title='%Unconditional%';
  ELSE SET u_title = CONCAT('%',u_title,'%');
END CASE;
SELECT T.Title,B.BAND_Name,C.Type,T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//

Mark

Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk



Thread
STORED PROCEDUREScott Hamm2 Aug
  • RE: STORED PROCEDUREMark Leith2 Aug
    • Re: STORED PROCEDUREScott Hamm2 Aug
      • RE: STORED PROCEDUREMark Leith2 Aug
        • RE: STORED PROCEDUREMark Leith2 Aug
          • Re: STORED PROCEDUREMartijn Tonies2 Aug
        • Re: STORED PROCEDUREScott Hamm2 Aug
  • Re: STORED PROCEDURESGreen2 Aug
    • Re: STORED PROCEDUREScott Hamm2 Aug