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.
Demo
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.
Conclusion
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.
Further Reading
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 