Discuss "Stored Procedures on MySQL" in the MySQL forum.

richyrich · #1 June 9th, 2008, 08:31 AM

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

Wolffy · #2 June 9th, 2008, 10:11 AM

Does mySQL have anything like the T-SQL PRINT statement? If so, put a PRINT stmt in before the EXECUTE to check that you sql statement is correct. I notice that you don't declare strsql -- T-SQL wouldn't like this.

mehere · #3 June 9th, 2008, 10:12 AM

i don't see anything different in what you're doing, then what i'm finding.

Code:

DELIMITER \\

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 stmt1 FROM @strSQL;
	EXECUTE stmt1;
	DEALLOCATE PREPARE stmt1;
END \\

DELIMITER ;

to use it, just

Code:

CALL clientlist('pc_user',5)

at least that's all it is according the mySQL manual.

Wolffy · #4 June 9th, 2008, 10:21 AM

I take back what I said about strsql -- I now see that @-sign syntax is a thing called global variables -- so different from T-SQL.

richyrich · #5 June 9th, 2008, 10:39 AM

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

richyrich · #6 June 9th, 2008, 10:43 AM

And for some reason it doesn't like DELIMITER

It says I have an error in my MySQL statement.

*sigh*

Wolffy · #7 June 9th, 2008, 11:09 AM

I think that delimiter is just if you are entering the sproc from the console line by line (the equivalent of iSQL) since the semicolon is required sytanx in the sproc itself.

richyrich · #8 June 9th, 2008, 11:15 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
If statement stored procedure	peebman2000	SQL Development	32	May 23rd, 2008 03:54 PM
Dynamic Stored Procedure	jmurrayhead	Microsoft SQL Server	16	March 26th, 2008 11:19 AM
Permissions on Tables, Stored Procedures, etc.	theChris	Microsoft SQL Server	2	March 24th, 2008 11:49 AM

The Following User Says Thank You to Wolffy For This Useful Post:
richyrich (June 9th, 2008)

Discuss "Stored Procedures on MySQL" in the MySQL forum.

MySQL - MySQL is a powerful open source database system most commonly used with PHP applications. Discuss MySQL administration and other MySQL related topics here.