SiteCrafting, Inc.

I was working on a content retrieval procedure recently, and hit a snag. I needed to retrieve the content of one field, do some other processing, then compare that content to a field in another table. That middle processing step was preventing me from just doing a JOIN between the two.

Logically, what I wanted to do was this:

BEGIN
    -- set up variable to store name of table
    DECLARE storedValue VARCHAR(64);
    
    -- query to grab the value used later
    SELECT firstValue
    INTO storedValue
    FROM FirstTable
    WHERE id = 1;

    -- other stuff happens here

    DECLARE tableCur CURSOR FOR
    SELECT *
    FROM SecondTable
    WHERE secondValue = storedValue;

    OPEN tableCur;
    tableLoop: LOOP

        -- process results

    END LOOP tableLoop;
    CLOSE tableCur;
END

Unfortunately this doesn't fly. Because the creation of the cursor is a DECLARE statement, it has to go before any actual processing in the block. I thought I was stuck: the cursor needed to come before other queries for syntax, but couldn't build its own query without data from another.

However, on a lark I decided to reverse the two, like so:

BEGIN
    -- set up variable to store name of table
    DECLARE storedValue VARCHAR(64);

    DECLARE tableCur CURSOR FOR
    SELECT *
    FROM SecondTable
    WHERE secondValue = storedValue;
    
    -- query to grab the value used later
    SELECT firstValue
    INTO storedValue
    FROM FirstTable
    WHERE id = 1;

    -- other stuff happens here

    OPEN tableCur;
    tableLoop: LOOP

        -- process results

    END LOOP tableLoop;
    CLOSE tableCur;
END

I was more than a bit surprised to discover that it worked perfectly. Apparently (this was news to me), when you use a local variable as part of a cursor definition, that variable is resolved not at declaration but at execution, when the cursor is open. Essentially the variable is passed into the cursor by reference, rather than value. Interesting stuff.

(I was eventually able to compose a more involved query that let me use a join, and cut out that middle step, but I suspect that the ability to use variables in cursors as placeholders to be populated later is one that will come in handy in the future.)