How to convert a Coldfusion loop constructing MySQL synatx into a MySQL stored procedure?

Question

I'm trying to move a product search from Coldfusion into a MySQL stored-procedure.

The search has about 20 criterias and is a vanilla-function of the site it's in, so wanted to convert it to a stored-proc, since there is a lot of pre-compiling having to be done.

I think I figured out most of the search criterias. I'm struggling with the last one.

First the Coldfusion part:

<cfquery datasource="dtb" name="get_pricelists">
  SELECT sid, pricelist
  FROM buyerList AS b
  LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
  WHERE b.bid = >parameter<
</cfquery>

This sellects all sellers using pricelists and the respective pricelist for the current user. Looks like this (in MySQL I'm creating a temp table):

=====================
sellerID    pricelist
12345         NULL
23467         foo
99999         bar

The next part is where I'm struggling:

<cfset misterLister = "LEFT JOIN preislisten p ON ">
<cfoutput query="get_pricelists" >
<cfif pricelist IS ''>
     <cfset misterLister = misterLister & '(p.sid = a.sid AND p.pricelist = "BASE" AND p.ean = a.ean AND p.iln = "#sellerID#") OR '>
  <cfelse>
     <cfset misterLister = misterLister & '(p.sid = a.sid AND p.pricelist = "#pricelist#" AND p.ean = a.ean AND p.iln = "#sellerID#") OR '>
  </cfif>
</cfoutput>
<cfset misterLister = misterLister & "(1=0)">

So with the above example, this would loop through the 3 found sellers and pricelists to create this MySQL syntax in Coldfusion:

LEFT JOIN pricelists p ON
   (p.sid = a.sid AND p.pricelist = "BASE" AND p.ean = a.ean AND p.iln = 12345 ) OR 
   (p.sid = a.sid AND p.pricelist = "foo" AND p.ean = a.ean AND p.iln = 23467 ) OR 
   (p.sid = a.sid AND p.pricelist = "bar" AND p.ean = a.ean AND p.iln = 99999) OR 
   (1=0)

which is then just passed into the actual query.

Question:
I can do the first part and store it in a temp table. But is it possible to create the 2nd part in MySQL, too, that is loop through the results of the temp table and construct the above statement from it?

I'm still MySQL newbie, so I'm not sure where to start. I'm looking at prepared statements and cursor, but are these the only options?

EDIT:
Ok. I have tried to come up with my first prepared statement. Looks like this:

SET @sql_text := '
DECLARE strCount   INT DEFAULT 1;

SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs
    FROM buyerList AS b
    LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
    WHERE b.bid = ?

SET @string = "LEFT JOIN preislisten AS p";

lj:
  LOOP

    SET @string = CONCAT( @string, "ON (p.iln = a.iln AND p.preisliste = sid AND p.ean = a.ean AND p.iln = pricelist ) OR");

    SET strCount = strCount+1;
    IF strCount = recs
    THEN LEAVE lj;
    END IF;

END LOOP lj;

SET @string = CONCAT( @string,"(1=0)")
';
SET @param_iln = param_iln;
PREPARE stmt FROM @sql_text;
EXECUTE stmt using @param_iln;
DEALLOCATE PREPARE stmt;

So I'm doing the inital query inside the prep statement string and then hoped to be able to loop over the found pricelists (it will be more than 3 sellers and pricelists, so I need to run through a loop, do I?). I concat everything together. But if this would work, how would I add this string to my actual search query, which will look like this:

SELECT articles AS art 
   << insert left join here >>
    FROM bigtable AS bt
    WHERE
      a lot of other criteria

I'm lost...

Answer 1

Replace SELECT sid, pricelist

With SELECT sid, ifnull(pricelist,'BASE')

(sql server readers, mysql's ifnull() is like your isnull() function)

This make pricelist come out as "BASE" when null.

You can then skip the <cfif pricelist IS ''> + <cfelse>

If you are using cf9+ you can also use shorthand concatenate <cfset misterLister &= "value">

You'll get better performance by building dynamic sql and executing inside stored procedure. Less database round trips. It's faster if coldfusion only has to talk to mysql once.

You wouldn't need a temp table to loop over. Just build the sql string to execute directly in the query. Then execute the sql string.

Don't worry about cursors - they are for when you want to load up some records, loop over them and perform an action on each row.

Prepared statements: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html

Stored procedures: http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

You basically want a stored procedure which you supply arguments. It internally builds up a string which is prepared and executed. Return the resulting data.