I would like to reply to each of you as you gave me a number of things to think about but instead, I will make some general comments.
I agree that bad code using temp tables, be they global or local, can seriously jeopardize the system, but I have learned over that last 6 years, since starting with SQL Server 6.5 to use them sparingly. Most importantly, I create them with specific fields in the stored procedure, and then use the INSERT INTO ... syntax rather than the SELECT INTO ... for the reason that the INSERT INTO ... avoids the locking problems nigelrivett spoke about. But in circumstances where you are preparing dynamic sql statements to be executed within the stored procedure, especially in situations where you might have a number of nested dynmaic sql executions, global temp tables come in very handy, especially when you are not sure of the actual number of fields that might need to be returned by the query when the database has been used for some time and changed over time. For example, I recently did a law enforcement database application accessing an SQL Server 2000 database I did not design for reporting purposes. This database had over 175 tables, and it used what the designer called a Universal Table to access certain data in other tables that could not be joined without first extracting the necessary keys from the Universal Table. In addition, the database allowed for certain tables containing fee category information for fines and payment types to change by adding or subtracting columns related to fee type. As a consequence, and in an attempt to write stored procedures that would not have to be re-written every time the database was changed as to fee categories, I had to create a series of dynamic SQL statments that would return global temporary tables containing all the necessary fee categories and the data pertaining to them, even though I had no idea as to the number of categories or their names. In other words, I treated the stored procedures like you would program a COM object, allowing for the adding of interfaces without changing the contract of the original interface the accessing program was looking for in retrieving information.
I do understand the problem with TempDB and the use of temp tables, but I have never had the occassion to use more than 4 temp tables in any stored procedure and always make it a practice to drop the temp table immediately after I am finished using it and not waiting to clear the TempDB at the end of the stored procedure.
I will certainly consider the option of doing all the calculating at the client level, but, as you know, the server generally is more powerful for these purposes.
I also make it a general practice to open and close connections to the database only when needed.
I appreciate all of the input here and thank you all for taking the time to answer my questions.