[thelist] SQL query execution order

[rudy]

> So, the question is (I suppose), how do I make sure that the
> conditional clauses run *before* the CONVERT statements
> happen so that my stored procedure won't try and convert the
> wrong data?

hi judah

i have this deal with database vendors -- i don't try to guess how their
optimizers work, and they don't try to guess what i really want in my
result set

i have two suggestions -- try moving row selection from WHERE into ON
conditions, and try parenthesizing your inner joins

e.g. change

        FROM responses
  INNER JOIN form_lookup
  INNER JOIN questions
          ON form_lookup.questionid = questions.questionid
          ON responses.questionid = form_lookup.questionid
       WHERE userid IN
              ( SELECT userid FROM usergroup_users
                 WHERE usergroupid = @usergroupid )
         AND form_lookup.formid = @formid
         AND ( questions.questionnumber BETWEEN @num1 AND @num2
           AND questions.status = 1 )

to

        FROM responses
  INNER JOIN

             form_lookup
  INNER JOIN questions
          ON form_lookup.questionid = questions.questionid
         AND form_lookup.formid = @formid )
         AND ( questions.questionnumber BETWEEN @num1 AND @num2
           AND questions.status = 1 )
             )
          ON responses.questionid = form_lookup.questionid
       WHERE userid IN
              ( SELECT userid FROM usergroup_users
                 WHERE usergroupid = @usergroupid )


rudy