Thread: Using Parameterized Stored Procedure

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

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?

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?

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

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?