Thread: Stored Procedures on MySQL

I thought I'd try adding some stored procedures to my MySQL db, to see if it improves performance.

I have a list of clients with a number of options to filter the list. At the moment I build the query as a string in .NET and then execute it on the db.

What I am trying to do is pass the various filter options to the SP and build the query in the SP.

How would you go about doing this in an SP?
And, how do you pass parameters to the query inside the SP? Is it the same as you'd pass them to a query?

I tried this simplified version from an example on the MySQL site, but it just returned "Number of rows affected:-1", which I believe means there's an error somewhere.

Code:

CREATE PROCEDURE clientlist
(pcuser INT, istatus INT)
 
BEGIN
DECLARE _status VARCHAR (50);
IF istatus = 5 THEN
       SET _status = ' AND visitors.status<>5';
ELSE
       SET _status = ' AND visitors.status=1';
END IF
 
SET @strsql = CONCAT('SELECT visitorref,forename,surname,clientstatus.status FROM visitors,clientstatus WHERE visitors.status=clientstatus.statusref',_status);
 
PREPARE stmt FROM @strsql;
 
EXECUTE stmt;
 
END;

Can anyone see what I'm doing wrong or provide an example?

Thanks

Yeah, I know. I've tried all sorts of things. It creates the procedure fine, but just won't run it.

I've even tried something really basic:-

Code:

CREATE PROCEDURE p4 ()
SELECT 'Hello, world';

which is straight out of a MySQL document.
And

Code:

CREATE PROCEDURE p4 ()
BEGIN
SELECT 'Hello, world';
END;

It creates the SProc fine but when I call it, I just get Affected rows: -1.

I've posted on the MySQL forum to see if they can suggest anything.

Very frustrating...

And for some reason it doesn't like DELIMITER

It says I have an error in my MySQL statement.

*sigh*

OK. I think I may have sorted it.

I downloaded the most recent MySQL Query browser, typed it into that and it executed with no problem.

Called the SProc and it worked fine.

Must be related to the software I was using. I found a post about a similar problem on the MySQL forum and thought I'd give it a go..

Strange how the software you're using can affect what gets saved but there you go!!

Shame I've wasted a whole day finding that out...