Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Help with FOR LOOP

    I have a piece of code that needs to loop inside of a SELECT statement. The static version looks like this:

    SELECT
    ROWID,
    HTMLContent1 =
    CASE
    WHEN CAST(RowID AS int) % (8) = 0 THEN HTMLContent8
    WHEN CAST(RowID AS int) % (8) = 1 THEN HTMLContent1
    WHEN CAST(RowID AS int) % (8) = 2 THEN HTMLContent2
    WHEN CAST(RowID AS int) % (8) = 3 THEN HTMLContent3
    WHEN CAST(RowID AS int) % (8) = 4 THEN HTMLContent4
    WHEN CAST(RowID AS int) % (8) = 5 THEN HTMLContent5
    WHEN CAST(RowID AS int) % (8) = 6 THEN HTMLContent6
    WHEN CAST(RowID AS int) % (8) = 7 THEN HTMLContent7
    END,
    HTMLContent2 =
    CASE
    WHEN CAST(RowID AS int) % (8) = 7 THEN HTMLContent8
    WHEN CAST(RowID AS int) % (8) = 0 THEN HTMLContent1
    WHEN CAST(RowID AS int) % (8) = 1 THEN HTMLContent2
    WHEN CAST(RowID AS int) % (8) = 2 THEN HTMLContent3
    WHEN CAST(RowID AS int) % (8) = 3 THEN HTMLContent4
    WHEN CAST(RowID AS int) % (8) = 4 THEN HTMLContent5
    WHEN CAST(RowID AS int) % (8) = 5 THEN HTMLContent6
    WHEN CAST(RowID AS int) % (8) = 6 THEN HTMLContent7
    END
    FROM Table2

    There could be any number of "HTMLContent" blocks which is why I need this to be in a loop.

    The dynamic version I'm working on looks like this:

    DECLARE @TotalCnt INT
    DECLARE @MyCounter INT
    SET @TotalCnt = 6
    SET @MyCounter = 0

    SELECT
    ROWID,
    HTMLContent1 =
    CASE
    WHILE (@MyCounter <= @TotalCnt)
    BEGIN
    WHEN CAST(RowID AS int) % (@TotalCnt) = @MyCounter THEN HTMLContent8
    SET @MyCounter = @MyCounter + 1
    END
    END
    FROM Table2

    While the static version works fine, I'm getting all kinds of syntax errors with the second (dynamic) version. Can someone please shed some light?

    TIA

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    To start, WHILE is invalid in a select statement. What are you trying to do? Return a variable number of columns? From the query, it looks like you have a very bad design for the table. The HTMLContent(n) should be broken off to a different table, instead of tacked along as more and more (and more, and more...) columns.

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    There can only be up to 8 HTMLContent columns, but there could be less, depending on the value of the @TotalCnt variable (1-8). Is there any way to do this? Maybe I need a cursor? I know they're bad, but I'm looking for a solution. Thanks for any feedback.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest that you take about three big steps backward so that you can take a good look at what you've got (the input) and what you want to get from it (the output). If you can explain those two things in terms of the "real world" instead of in terms of how you've already laid out the table, I think we can get a whole lot closer to helping you solve your problem.

    With that said, I'll take a shot at something that approximates what I think you are trying to get... Keep in mind that this is only a SWAG at a first cut, it may not even resemble what you really want:
    Code:
    SELECT rowid, 1 AS colid, HTMLContent1
       FROM Table2
    UNION SELECT rowid, 2, HTMLContent2
       FROM Table2
       WHERE  HTMLContent2 IS NOT NULL
    UNION SELECT rowid, 3, HTMLContent3
       FROM Table2
       WHERE  HTMLContent3 IS NOT NULL
    UNION SELECT rowid, 4, HTMLContent4
       FROM Table2
       WHERE  HTMLContent4 IS NOT NULL
    UNION SELECT rowid, 5, HTMLContent5
       FROM Table2
       WHERE  HTMLContent5 IS NOT NULL
    UNION SELECT rowid, 6, HTMLContent6
       FROM Table2
       WHERE  HTMLContent6 IS NOT NULL
    UNION SELECT rowid, 7, HTMLContent7
       FROM Table2
       WHERE  HTMLContent7 IS NOT NULL
    UNION SELECT rowid, 8, HTMLContent8
       FROM Table2
       WHERE  HTMLContent8 IS NOT NULL
       ORDER BY 1, 2
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •