List:General Discussion« Previous MessageNext Message »
From:Magne Westlie Date:February 7 2008 6:08pm
Subject:Re: stored procedure, parameter type help needed
View as plain text  
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