Discuss "Using Parameterized Stored Procedure" in the MySQL forum.

richyrich · #11 June 11th, 2008, 09:29 AM

The reason I was looking at the SP was this particular query always seems to take quite a long time to run in .NET, whereas when I tested it in the query browser it was taking less than half a second.

I'm sure the delay is DB related as other queries to the same table are much quicker...

Wolffy · #12 June 11th, 2008, 10:10 AM

RR;
I didn't take the time to unravel all the logic in your SP, but it appears in mySQL that you can PREPARE..EXECUTE a query whilst substituting parameters. In you case, consider if you can build your dynamic query in such a way to use parameters in the EXECUTE statement rather than building them into the query. Such as the very simple example:

Code:

     SET @s = 'SELECT * FROM authors WHERE id BETWEEN ? AND ?'; 
    SET @min = _min; 
    SET @max = _max; 

    PREPARE stmt1 FROM @s; 
    EXECUTE stmt1 USING @min, @max; 
    DEALLOCATE PREPARE stmt1;

richyrich · #13 June 11th, 2008, 02:28 PM

Yeah I did see that Wolffy, but wasn't sure if I could get the order of the parameters right. I assume it drops the USING values into the query in the order they are placed.

I was wondering if you could do the same but name them.

Wolffy · #14 June 11th, 2008, 02:58 PM

Yeah, now I see what you mean. I was trying to figure out a way to do this while having the same parameters in the same position regardless of query -- but that's going to make for some really inefficient queries. Take the rather simple example of your conveyor predicate:

Code:

IF iconveyor <> '' AND iconveyor<>'all' THEN
 SET _conveyor = ' AND visitors.conveyor = ?';
ELSE
 SET _conveyor = ' AND visitors.conveyor LIKE ?;
 SET @iconveyor = '%';
END IF;

Consider all the permutations, and you could have a ton of LIKE's in the query. Gross

richyrich · #15 June 12th, 2008, 07:00 AM

I think I got it sorted.

No changes to my SP. In .NET used the following code to add the parameters:-

Code:

Dim mycommand as new mysqlcommand("clientlist",conn)
Dim isurname as MySqlParameter = mycommand.parameters.Add("isurname",mysqldbtype.varchar)
isurname.value = search.surname

So, you need to create an instance of a mysql parameter object and set it to an IN parameter of the SP. You need to create an object for all the IN parameters you have in your SP. Then assign the value you want to it. In my case it's a property of a search class I have.

Hope that helps someone...

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
Stored Procedures on MySQL	richyrich	MySQL	7	June 9th, 2008 11:15 AM
Parameterized Dynamic Query	Shem	.Net Development	4	June 6th, 2008 10:20 AM
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

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

Discuss "Using Parameterized Stored Procedure" 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.