#0254-SQL Server 2000-Alternate to Table Valued Parameters-Share Temporary Tables with a stored procedure


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:

  1. Open a SQL Server connection, say C1
  2. Create the temporary table
  3. Insert data into the temporary table
  4. Call the stored procedure for the desired computation
  5. Fetch the value/result once the stored procedure execution is complete
  6. Drop the temporary table
  7. 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.

image

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.

Advertisements

4 thoughts on “#0254-SQL Server 2000-Alternate to Table Valued Parameters-Share Temporary Tables with a stored procedure

  1. Nakul Vachhrajani

    @Jeff: Thank-you for your time and feedback!

    Having worked on SQL Server 2000 in the initial stages of my career, it took me quite a while to get used to table valued parameters. Perhaps the only immediate benefit visible to application developers is that they don’t need to take care of explicitly dropping the temporary table. This point almost always makes the developers jump for table valued parameters. In my case, it was the application developers who made me change from temporary table to table valued parameters for generic patterns (two int columns, one int one date column and so on).

    Like

    Reply
  2. Jeff Moden

    Since closing the conection in Step 7 effectively ends the session, you don’t have to drop the Temp Table. It will drop all by itself. As a matter of fact, if the script is used a whole lot, there’s a benefit to NOT explicitly dropping the Temp Table. Microsoft made it so that, like an execution plan, the system will remember the empty table structure and reuse it for a bit more performance.

    The only thing else that I might add is that I’d likely use SELECT UNION ALL to drive the INSERT instead of multiple INSERT/VALUE statements. Each INSERT/VALUE statement will either create a separate execution plan or the system has to decide if there’s an execution plan that can be reused for each and every INSERT/VALUE statement. That, of course, takes relatively much more time than a single INSERT.

    Since it’s also likely that the data in the Temp Table was carefully selected and that all of the data will be used, you might also consider using SELECT/INTO to create the Temp Table which will be much faster than inserting into a pre-existing Temp Table. Agreed that SELECT/INTO won’t create a PK as you’ve done in your fine demonstration but, because the data was carefully selected as previously stated, there’s a very, very high probability that the optimizer will choose to do table scans instead of using the index, anyway. In fact, in some cases (you always have to test), the unindexed Temp Table may actually outperform the indexed version in joins or whatever the stored procedure is doing because the optimizer will automatically know that it’s going to have to do a scan. I proved that in one of the million row tests I did for my articles on Hierarchy conversions.

    But, again, nice blog entry. I use many of the “old ways” because they faster and easier to use than some of the “new” ways. It’s good to see someone post about the old ways because people forget about them or think they’re “not cool”. Thank YOU for taking the time to post such things.

    Liked by 1 person

    Reply
  3. Pingback: #0364 – SQL Server – Using Primary Key, Check and other Constraints with User Defined Table Types (UDTT) | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s