A few days ago there was an article with 20 tips to write a good stored procedure (requires free registration to read). The problem is that there are really only 12 good tips (and 4 bad and 4 neither good or bad). So let me go over the tips one by one and comment on them:
6. "# Set NOCOUNT off. Good advice: Score: 3-1-2"
That should be "Set NOCOUNT ON".
9. "The problem with select is that the variable might not be set if a query returns no rows"
In the example, there is no query:
SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 - 1
SET @Var1 = @Var1 + 1
SET @Var2 = @Var2 - 1
11. "Avoid OR in WHERE clause."
The example given results in a non-sargable query, which is *bad* for performance:
SELECT emp_name FROM table_name WHERE LOWER(emp_name) = 'edu'
The query optimiser cannot use an index on the emp_name column to satisfy this query. Either use a case-insensitive collation, or create a persisted computed column to store the normalized data.
Also, normalizing strings to lower case is a bad idea - you should always normalize to upper case.
Thanks for pointing out the typo in 6.
Regarding 9, I just wanted to point out a common pitfall when using select leaving variables unchanged. If the select is just a way to group a number of set it is not that big deal I think, but in my experience variables are typically set from values being selected from tables rather than not.
You comment on OR is correct. The example given is not that great, but the basic idea of not using OR is still a good advice I think. From a general perspective I mean.
Good analysis... thank u for the Corrections You Specified.