Great stuff, thanks much Mark. A related question, I've been moving code that is in a procedure 3 or 4 times to a separate stored procedure as I modify someone elses code. What are your suggestions for keeping the performance of this the same?

We're trying using console variables (create variables) for this, do they have overhead associated with them that makes this a bad practice? Besides being global and thus a bad practice already. :)

The cost of 'executing a sequence of statements' and 'calling a procedure that executes the sequence of statements' is obviously not zero since there is the additional cost of doing the call. Whether this additional cost is significant will be dependent on the frequency of usage and the relative cost as compared to the total cost of the complete operation. So you need to weigh the factors and determine what is best for you circumstance.

Whether or not to use global variables is a choice that you will need to make - there is likely minimal performance differences except for the obvious cost savings for not needing to pass the parameter(s). i.e. each procedure is only parsed and analyzed once so once a variable reference is bound to its (global or parm) value there is no difference on how it is used.

The only overhead of global variables is that the variable definitions is kept at the connection level and persists for the life of the connection. (continued next comment....)

If you have a long living connection then you may want to consider setting the global variable value to null after you are finished using it so that its associated resources are mimimum - i.e. if you set a global to a long long string (e.g. during processing of a particular request) and then do not use it again, the variable will continue to use memory resources while the connection remains action. (Note that memory assigned to an inactive connection will eventually be paged to the db's temp file)