I also missed the assignment back to the int variable. Oops. It would have been an obvious answer if only I'd paid better attention.

SELECTing into a variable is not being deprecated. But there are a few things to know when deciding whether to use SET or SELECT:

1) SET is the ANSI standard for assigning variable values. So if you care about that, there's your answer.

2) When assigning a value from a query, SET will raise an error if multiple rows are returned. SELECT will simply assign the last returned value and go about its merry way.

3) If the query returns zero rows, SET will set the variable to NULL. SELECT will leave the variable set to its current value.

4) SELECT can assign values to multiple variables simultaneously. So if you have three variables that each need a value from a given table, you'd have to query the table three times using SET, and only once with SELECT. Obviously this would have performance benefits in certain scenarios.

The conventional wisdom, as far as my research indicates, is that SELECT is preferable when you need to set multiple variables from a single query. SET is preferable when setting a single variable, or if ANSI-compliance is a requirement.

Ron Moses