Temp Tables in SQL 2000

I had a hard time remembering how to do this and was not using the right keywords in Google.

There are a few ways to do temp tables in SQL. In SQL7 we could use a #tablename, but there are issues with that. It becomes part of the returned resultset and you have to remember to delete them.

In SQL2000, you can still use that syntax but in many cases a table variable is a much better route. It is a variable declared in your stored procedure, it is local so you don’t have to worry about deleting it and it does not affect the result set.

NOTE: You must include “SET NOCOUNT ON“ at the beginning of your stored procedure to prevent that extra gunk in the result set that ADO retrieves. I found that this works with table variables, but it does not fix the problem with #temptables.

Here’s a useful article on it: http://www.sqlteam.com/item.asp?ItemID=9454 with syntax and caveats.

I had a sproc using the #temptable method that worked just fine here on my test server which is SQL2000. When I deployed it to my client’s production server, also SQL2000 with latest service packs, ADO (in VB6 , not ado.net) was having an issue against the production database with recordset.next that did not show up when I ran against the local database. So I went with the table variable instead.

Posted from BLInk!

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.