Another Attack of Colossal Stupidity
Like you, I also get the daily newsletter in my Inbox. I write and submit the editorial, and SSWUG builds everything else.
I saw a link yesterday for one of the most frequent searches this past month, “SPOOL OUTPUT SQL SERVER”, and decided to click on it. Lo and behold, I come across a forum thread entitled “Another Attack of Colossal Stupidity”. How can you see that and not look? So I did.
The thread was about sharing a temp table across stored procedures. This is a great SQL Server technique for sharing sets, but still separating your code into different procedures. With the advent of SQL Server 2008 you can use input table parameters for stored procedures. But shared temp tables still have a roll, and sometimes better performance.
Here is one way it works.
Procedure 1
Create Table #Temp (…)
Call Procedure 2
insert some data into #Temp
Process data inserted into #Temp by Procedure 2
Here is another method of sharing data across stored procedures.
Procedure 1
Create Table #Temp(…)
Add Data to #Temp
Call Procedure 2
Do something with data in #Temp
You can nest this calling as many layers deep as needed. Since #Temp is not a virtual table in memory but a real table in tempDB, you have the ability to create indexes, constraints, etc. on the table should it be necessary for optimization.
So, what was the stupidity? Well, actually, the developer was right on track but couldn’t find the exact syntax needed to determine if the temp table already existed. When you share a temp table across stored procedures, it is a good practice to test for the existence of the temp table prior to using it in order to reduce false errors. In this case, testing for the temp table has a little different syntax than you might expect.
When you create a temp table, the temp table created in tempDB actually has a little different table name than the one you specify. This is used to help separate your temp table from one created by another session with the same name. your table name has a suffix appended to the name making it unique.
If you wanted to find the existence of a temp table in your process called #Test, and know that it was your instance, and not one belonging to someone else, you use the following syntax.
IF OBJECT_ID(tempdb..#Test) IS NOT NULL
This test resolves to tempdb.dbo.#Test[Your specific process instance]. You don’t have to worry about the magic used by SQL Server generating a unique table name for each process instance.
That’s my tip for the day…an oldie but goody. Feel free to drop me other tips you think would be helpful to our readers.
Send your contributions to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting Upgrading to SQL Server 2008
In this article, Alexander Chigrik explains some problems that you can have when you upgrade to SQL Server 2008. He also tells how you can resolve these problems.
Featured White Paper(s)
Structuring the Unstructured: How to Dimensionalize Semi-Structured Business Data
Written by Interactive Edge
The Business Intelligence industry … (read more)