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

richyrich · #1 June 11th, 2008, 07:12 AM

I'm just learning to use Stored Procedures with MySQL.

What I'm trying to do is write an SP that returns a client list based on filters passed to it. So I have to build the Query to execute based on the values passed to it.At the moment I have the following code:-

PHP Code:

  DELIMITER $$
 
DROP PROCEDURE IF EXISTS `policycheck`.`clientlist` $$

CREATE PROCEDURE `clientlist`(istatus INT,iconveyor VARCHAR (20),icampaign INT,isurname VARCHAR (50),icoord INT,iaccess VARCHAR(20),iunseen VARCHAR (3),imonth INT, iyear INT,isortby VARCHAR (15),isortbydir VARCHAR(10))

BEGIN
 
DECLARE _status VARCHAR (50);
DECLARE _conveyor VARCHAR (50);
DECLARE _campaign VARCHAR (50);
DECLARE _surname VARCHAR (50);
DECLARE _coord VARCHAR (100);
DECLARE _month VARCHAR (50);
DECLARE _year VARCHAR (50);
DECLARE _sortby VARCHAR (50);
DECLARE _sortbydir VARCHAR (5);
SET @istatus = istatus;
SET @iconveyor = iconveyor;
SET @icampaign = icampaign;
SET @isurname = CONCAT(isurname,'%');
SET @icoord = icoord;
SET @imonth = imonth;
SET @iyear = iyear;

IF istatus <> -1 THEN
  IF iunseen = 'yes' THEN
   SET _status = ' AND visitors.status <> 5';
  ELSE
   SET _status = ' AND visitors.status = @istatus';
  END IF;
ELSEIF (icoord<>0 AND (iaccess <> 'AdviserLtd' AND iaccess <> 'AdminLtd')) THEN
  SET _status = ' AND (visitors.status<>5 and visitors.status<>2 and visitors.status<>3)';
ELSEIF imonth<>0 OR iyear<>0 THEN
  SET _status = ' AND (visitors.status<>5 and visitors.status<>7)';
ELSE
 SET _status = ' AND visitors.status <> 5';
END IF;
IF iconveyor <> '' AND iconveyor<>'all' THEN
 SET _conveyor = ' AND visitors.conveyor = @iconveyor';
ELSE
 SET _conveyor = '';
END IF;
IF icampaign <> 0 THEN
  SET _campaign = ' AND visitors.campaign_id=@icampaign';
ELSE
  SET _campaign = '';
END IF;
IF isurname <> '' THEN
  SET _surname = ' AND surname LIKE @isurname';
ELSE
  SET _surname = '';
END IF;
IF imonth <> 0 THEN
  SET _month = ' AND month(visitors.dateadded)=@imonth';
ELSE
  SET _month = '';
END IF;
IF iyear <> 0 THEN
  SET _year = ' AND year(visitors.dateadded)=@iyear';
ELSE
  SET _year = '';
END IF;
IF imonth<>0 OR iyear<>0 THEN
  SET _sortby = ' ORDER BY visitors.visitorref';
ELSE
CASE isortby
  WHEN 'Ref' THEN SET _sortby = ' ORDER BY visitors.visitorref';
  WHEN 'Forename' THEN SET _sortby = ' ORDER BY visitors.forename';
  WHEN 'Conveyor' THEN SET _sortby = ' ORDER BY conveyors.conveyorname';
  WHEN 'Status' THEN SET _sortby = ' ORDER BY clientstatus.status';
  WHEN 'Phase' THEN SET _sortby = ' ORDER BY entryexitphase.phase';
  ELSE SET _sortby = ' ORDER BY visitors.surname';
END CASE;
END IF;
IF isortbydir='Descending' THEN
 SET _sortbydir = ' DESC';
ELSE
 SET _sortbydir = ' ASC';
END IF;
SET @sql = CONCAT('SELECT visitorref,surname,forename,invoicecity,invoicepostcode,conveyors.conveyorname As eval_conveyor,clientstatus.status,entryexitphase.phase FROM visitors,conveyors,clientstatus,entryexitphase WHERE conveyors.conveyorref=visitors.conveyor AND clientstatus.statusref=visitors.status AND entryexitphase.phaseorder=visitors.phase',_status,_conveyor,_campaign,_surname,_month,_year,_sortby,_sortbydir);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

But, if I use O'Connor in the surname parameter, it says I have an error in my SQL statement. This is when I pass it using the Query browser, not from my .NET code. Do I need to change anything in the SP to allow for single quotes or will this all be fine when I call it from my .NET code using parameters?

Also, how can I alter this SP so that it adds in the value to the query like it would adding a parameter in .NET? Eg.

Code:

mycommand.Parameters.AddWithValue("surname",mysurnamevar)

I've seen examples that say you use something like this in .NET:-

Code:

mycommand.Parameters.Add("?surname",mysurnamevar)

But then, how do you use this value in your code? Do I just change this:-

Code:

  SET _surname = ' AND surname LIKE @isurname';

to this:-

Code:

  SET _surname = ' AND surname LIKE ?surname';

Any help would be appreciated. BTW, MySQL SP's are pretty much the same as in other DBMs so any MS SQL examples/help should be fine.

Thanks

jmurrayhead · #2 June 11th, 2008, 07:44 AM

Well, since I haven't used MySQL for development in so long, I probably won't be of much help. But in MS SQL, we have a Replace function that could be used to double up the single quotes in the query. See if you can find a similar function for MySQL.

As far as the parameters...that would seem logical to do it that way, but I can't be sure.

richyrich · #3 June 11th, 2008, 07:47 AM

Yeah, MySQL also has replace, but I think it's only that I'm testing the SP by calling it in query browser with O'xxx as the parameter.

I don't think it's a problem with the SP itself, because it'll be fine if I pass the value as a parameter. How would you do that with MS SQL and .NET?

jmurrayhead · #4 June 11th, 2008, 08:07 AM

Since you're building the query inside the stored procedure by concatenating strings, I wouldn't think it would work. I thought so, too, using MS SQL, however it generated errors, so I had to double up the single quotes.

To add parameters using MS SQL, I use:

Code:

cmd.parameters.AddWithValue("@ParameterName", parameterValue)

richyrich · #5 June 11th, 2008, 08:10 AM

Quote:

Originally Posted by jmurrayhead

Since you're building the query inside the stored procedure by concatenating strings, I wouldn't think it would work. I thought so, too, using MS SQL, however it generated errors, so I had to double up the single quotes.

To add parameters using MS SQL, I use:

Code:

cmd.parameters.AddWithValue("@ParameterName", parameterValue)

And then how do you use this value in your SP?

jmurrayhead · #6 June 11th, 2008, 08:18 AM

When building strings, think of @parameterName as a variable in .Net. You have to concatenate it with the string, not place it between the quotes. So:

Code:

SET @strSQL= @strSQL + ' AND surname LIKE ''' + REPLACE(@isurname,'''','''''') + ''' '

richyrich · #7 June 11th, 2008, 08:33 AM

I thought using parameters meant you didn't have to worry about replacing single quotes?

jmurrayhead · #8 June 11th, 2008, 08:36 AM

Quote:

Originally Posted by richyrich

I thought using parameters meant you didn't have to worry about replacing single quotes?

Typically, yes...

But since you're building them as a string inside the stored procedure, the same rules apply as if you were building a SQL string in code in VB.Net, VBScript, C#, or whatever...

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

Hmmm.....Is there much advantage to using a Stored Procedure in this case then?

What about if I built the SQL in .NET and then passed that to a stored procedure and then added the parameters?

jmurrayhead · #10 June 11th, 2008, 09:20 AM

Quote:

Originally Posted by richyrich

Hmmm.....Is there much advantage to using a Stored Procedure in this case then?

What about if I built the SQL in .NET and then passed that to a stored procedure and then added the parameters?

Honestly, I don't know. I prefer to keep my database queries separate from my code. If you're going to build the query in code, then I don't see a need to pass it to a SP to execute, though. Just execute it from code in that case.

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