Monday, July 22, 2013

Iteration in SQL

...is a horrible, horrible bastard. Especially if you have to do table updates based on a condition. I'm not even sure if the function is native to SQL, but for the most part, it seems to work - even if it's extremely resource intensive.

The thing is I have to wrap my head around a concept before I could actually write my own code using said concept. I have been using someone else's code for a few months but unfortunately, that could only go so far in terms of customisation. After all, written code is based mostly on an individual's logic - and picking the maze inside someone's head is never a cakewalk.

Anyway, I kind of finally figured out how to do it in SQL, but the logic is such a clusterfuck that I have to write it down to remember it.

For personal reference, I've written the sample code below - with my not so helpful comments.

/* CODE BEGINS HERE */
-- the temp table for this code contains four fields
-- prj_id (FLOAT)
-- prj_pid (FLOAT)
-- vendor_name (VARCHAR)
-- matter_name (VARCHAR)

-- the code aims to insert the vendor_name (some sort of parent folder)
-- into each entry for matter_name (child folder)

-- Declare temporary variables. One is to be used as the iteration condition counter, which
-- would determine what to look at (and when the code will terminate). The others
-- could be used to store values needed in the UPDATE statements, where applicable.
DECLARE @prj_pid FLOAT
DECLARE @prj_pname VARCHAR(255)

-- Declare the cursor
DECLARE client_cursor CURSOR FOR
SELECT DISTINCT prj_pid FROM #temp

-- initialize cursor value by pulling first valid table entry
OPEN client_cursor
FETCH NEXT FROM client_cursor INTO @prj_pid

-- begin iteration
WHILE @@fetch_status = 0
BEGIN

SELECT @prj_pname = prj_name FROM dbo.projects
WHERE prj_id = @prj_pid

UPDATE #temp
SET matter_name = @prj_pname
WHERE prj_pid = @prj_pid

FETCH NEXT FROM client_cursor INTO @prj_pid
END

-- I assume this removes the object from memory
CLOSE client_cursor
DEALLOCATE client_cursor

/* CODE ENDS HERE */

It's highly likely (actually I'm pretty certain) that there's a more efficient way of doing this, but I'm sticking to what works for now.

Later!

2 comments:

  1. Coding is definitely Greek to me, and I've never taken interests in such endeavor. However, I read in an article, that coding is one of the skills that wannabe-entrepreneurs should learn and develop. Until now, I still don't get why, do you have any idea why that is?

    I even asked our IT but we had a challenging time understanding each other. HAHAHAHAHAHA

    ReplyDelete
    Replies
    1. To be quite honest, I have no clue. If you could point me to the article, I might have some thoughts.

      Well, that is, unless you're learning how to design websites for marketing or advertising or similar whatnot.

      Delete