List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 7 2008 8:45pm
Subject:Re: stored procedure, parameter type help needed
View as plain text  
Hi Magne

 >...the query I actually want to use this in, is a 100 line query with 
lots of arguments.
 >I don't feel to good about creating it into a bunch of strings (16) 
that I have to
 >concatenate with the variables inbetween.

The only alternative I know for current versions of MySQL is to assemble 
the query in the app layer.

 >Also, I was moving the query into a stored procedure because I wanted 
to make
 >the request fast, and the concatenating and string handling takes some 
of that away.

Unfortunately MySQL sprocs do not yet deliver this advantage.

PB

-----

Magne Westlie wrote:
> Hi,
>
> Thanks a lot Peter, that was useful and it worked fine. The only 
> problem is that the query I actually want to use this in, is a 100 
> line query with lots of arguments. I don't feel to good about creating 
> it into a bunch of strings (16) that I have to concatenate with the 
> variables inbetween.
> Also, I was moving the query into a stored procedure because I wanted 
> to make the request fast, and the concatenating and string handling 
> takes some of that away.
>
> Is there another way?
>
> Magne
>
>
>
> Peter Brawley wrote:
>> Magne,
>>
>> Sorry, the server is down at the moment, here is the entry ...
>>
>> To have an sproc accept a variable-length parameter list for an 
>> |IN(...)| clause in a query, code the sproc to |PREPARE| the query 
>> statement:
>> |
>> DROP PROCEDURE IF EXISTS passInParam;
>> DELIMITER |
>> CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param 
>> VARCHAR(1000) )
>> BEGIN
>>  SET @qry = CONCAT( qry, param, ')' );
>>  PREPARE stmt FROM @qry;
>>  EXECUTE stmt;
>>  DROP PREPARE stmt;
>> END;
>> |
>> DELIMITER ;
>> |
>> For this example, the query string should be of the form:
>> |
>> SELECT ... FROM ... WHERE ... IN ( |
>> but so long as it has those elements, it can be as complex as you 
>> like. When you call the sproc:
>> 1. Quote each argument with a /pair/ of single quotes,
>> 2. Separate these quoted arguments with commas,
>> 3. Surround the whole |param| string with another set of single quotes:
>> |
>> CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (', 
>> ('''abc'',''def'',''ghi''' ); |
>> ||
>> ||||PB
>>
>>
>>>
>>
>
>
Thread
stored procedure, parameter type help neededMagne Westlie7 Feb
  • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
  • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
    • Re: stored procedure, parameter type help neededMagne Westlie7 Feb
      • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
        • Re: stored procedure, parameter type help neededMagne Westlie8 Feb
          • RE: stored procedure, parameter type help neededJerry Schwartz8 Feb
            • Re: stored procedure, parameter type help neededMagne Westlie11 Feb
              • Re: stored procedure, parameter type help neededMoon's Father12 Feb