Support for Microsoft SQL Server 2000 ends this year and yet, one can find systems (production or otherwise) that continue to run on and support SQL Server 2000. Recently, we were tasked with porting a piece of functionality backwards, i.e. from versions that support SQL Server 2012/2008 to SQL 2000/2005. Unfortunately, this piece of functionality was designed to work with newer versions of Microsoft SQL Server and involved the use of table valued parameters which is not possible in SQL Server 2000.
After a couple of hours of brain-storming, we realized that we could share temporary tables with the underlying stored procedures as long as the connection remained the same. Because the connection that created a temporary table is still active, the temporary table will continue to be accessible if the stored procedure is executed on the same connection. Here’s a small demo to help explain the solution.
For this demonstration, we will create a simple mathematical temporary table which will be updated from within a stored procedure. Assuming that the temporary table is named #tTableValues and has columns iA, iB and iC ( = iA * iB), let us first create a stored procedure which is designed to update the value of iA:
USE tempdb ; GO IF OBJECT_ID('UpdateTableValues') IS NOT NULL DROP PROCEDURE dbo.UpdateTableValues GO CREATE PROCEDURE dbo.UpdateTableValues @replacementValue INT AS BEGIN SET NOCOUNT ON ; IF OBJECT_ID('#tTableValues') IS NOT NULL BEGIN UPDATE tv SET tv.iA = @replacementValue FROM #tTableValues AS tv END ELSE BEGIN PRINT 'Temporary table: #tTableValues does not exist or access denied.' END END GO
As you can see from the T-SQL script above, the stored procedure assumes that the temporary table is already available to it and directly makes the update. All the calling application now has to do is:
- Open a SQL Server connection, say C1
- Create the temporary table
- Insert data into the temporary table
- Call the stored procedure for the desired computation
- Fetch the value/result once the stored procedure execution is complete
- Drop the temporary table
- Close connection C1
The T-SQL script below demonstrates this workflow:
--1. Connection has been created USE tempdb ; GO --2. Check for and create the required temporary table IF OBJECT_ID('#tTableValues') IS NOT NULL DROP TABLE #tTableValues GO CREATE TABLE #tTableValues ( Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, iA INT, iB INT, iC AS iA * iB ) ; GO --3. Insert some test data INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 1 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 2 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 3 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 4 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 5 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 6 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 7 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 8 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 9 ) ; INSERT INTO #tTableValues ( iA, iB ) VALUES ( 1, 10 ) ; GO --Debug point 01: Select from the temporary table SELECT 'Debug Point 01' AS StepName, Id, iA, iB, iC FROM #tTableValues ; GO --4. Execute the stored procedure --NOTE that the connection has to be the same! EXEC dbo.UpdateTableValues @replacementValue = 2 ; GO --5. Fetch the result of the stored procedure execution SELECT 'Step 05 - Results' AS StepName, Id, iA, iB, iC FROM #tTableValues ; --6. Cleanup - the connection can now be closed IF OBJECT_ID('#tTableValues') IS NOT NULL DROP TABLE #tTableValues GO
The screen-shot shown below shows the output of the execution and it can be seen that the value for iA in the temporary table created by the calling application was indeed updated by the stored procedure and the results were available after the stored procedure execution completed.
As demonstrated, temporary tables may be used for data exchange between an application and stored procedures or even between any two programmability objects (procedures, functions, etc). This functionality continues to remain valid even in the days of SQL Server 2012. The question, however is whether it would be preferable to use newer features like table valued parameters or not. Do let me know your thoughts on the same.
As for those who need to continue support of Microsoft SQL Server 2000 for their products, I trust this post was helpful.
- Temporary Table Naming – Maximum Allowed Length and associated naming logic
- Microsoft SQL Server 2000 Support Life Cycle
Until we meet next time,