[thelist] SQL query execution order

[Judah McAuley]

Howdy folks,

I've got a nasty little error popping up with a stored procedure that
I've inherited from someone else who is now long gone.  This system runs
a survey tool.  Most of the responses are radio button, numeric choices.
  At the end of each survey there are a few open text questions for
feedback.  So the response column in the responses table is of type
varchar although its mostly populated with numeric data.

Here's the problem:  I've got a stored procedure that is supposed to
compute an average of responses to a subset of questions for a number of
users.  The full SQL statement is as follows:

SELECT CONVERT(float,SUM(CONVERT(int,responses.response))) /
COUNT(responses.response) AS Mean
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)

I get an error returned when this query is run against any user that has
filled in any of the free text questions because it tries to cast a
non-numeric value to an int.  But the CONVERT statements should happen
*after* the WHERE clause and the WHERE clause eliminates all of the
non-numeric answers.

If I change
SELECT CONVERT(float,SUM(CONVERT(int,responses.response))) /
COUNT(responses.response) AS Mean

to SELECT responses.response

I get all the correct responses back, all of which are numeric.

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?

TIA,
Judah

PS, running SQL Server 2000 Enterprise under Win2K Server.