How to Drop Table Variables in SQL-Server? Should I Even Do This

How do I drop table variables in SQL-Server? Should I even do this?

Table variables are automatically local and automatically dropped -- you don't have to worry about it.

Truncate/Clear table variable in SQL Server 2008

just delete everything

DELETE FROM @tableVariable

SQL Server - Delete from table variables not instantaneous?

From what it looks like you've inherited a "poor man's cursor". Somehow people heard that cursors are 'evil' and they then come up with this =(
I'm not going to start a debate on how set-based is preferred over cursor-based (read: line by line) operations. In some situations you simply have no choice; maybe this is one too.

Converting your loop to a decent cursor would probably already 'stabilize' that part of the loop; but it also immediately shows there is a bit a of 'problem' with your loop.

On first sight, the equivalent cursor would be this:

DECLARE @uniqueId int
DECLARE @examId int

DECLARE @TempSingleContactTable TABLE
(
uniqueId int IDENTITY(1,1) PRIMARY KEY,
examId int not null,
contactEmail nvarchar(max) null
)

-- [data inserted into @TempSingleContactTable]

DECLARE exams_loop CURSOR LOCAL FAST_FORWARD
FOR SELECT uniqueId, examID
FROM @TempSingleContactTable
OPEN exams_loop
FETCH NEXT FROM exams_loop INTO @uniqueId, @examId
WHILE @@FETCH_STATUS = 0
BEGIN

-- internals...

FETCH NEXT FROM exams_loop INTO @uniqueId, @examId
END
CLOSE exams_loop
DEALLOCATE exams_loop

But when looking more closely there is a catch: the end of your loop deletes all records for a given examID. So if there are multiple records with the same examID, this means that some uniqueID values would be skipped. (remark: it's not even certain which ones, never ever be tempted to rely on them being in natural order because there is a PK on the field!)

As such the following code is a better replacement:

DECLARE exams_loop CURSOR LOCAL FAST_FORWARD 
FOR SELECT MIN(uniqueId), examID
FROM @TempSingleContactTable
GROUP BY examID
OPEN exams_loop
FETCH NEXT FROM exams_loop INTO @uniqueId, @examId
WHILE @@FETCH_STATUS = 0
BEGIN

-- internals...

FETCH NEXT FROM exams_loop INTO @uniqueId, @examId
END
CLOSE exams_loop
DEALLOCATE exams_loop

This time it will indeed be the lowest uniqueID that wins out instead of a random one, but in all fairness I think repeatability (which is what we're talking about here really) is to be preferred over randomness.

Anyway, in summary :

  • rather use a real cursor instead of a poor-man's replacement because it's a bad replacement to start with
  • if you really want to keep the loop as it is right now, change the table-definition to this:

=>

DECLARE @TempSingleContactTable TABLE 
(
uniqueId int IDENTITY(1,1) PRIMARY KEY,
examId int not null UNIQUE (examId, uniqueId),
contactEmail nvarchar(max) null
)

this way you'll at least have an index on the field when you're deleting. (even though I'd highly discourage intensive operations on @table-variables, they tend to go south once you put 'medium' amounts of data in there, let alone start doing operations on it... #temp-tables are much more robust in that respect!)

What are the dangers of BEGIN TRY DROP TABLE?

What possible dangers? What unexpected errors are likely to be concealed?

If try catch block is inside a transaction, it will cause a failure.

BEGIN
BEGIN TRANSACTION t1;
SELECT 1

BEGIN TRY DROP TABLE #Results END TRY BEGIN CATCH END CATCH

COMMIT TRANSACTION t1;
END

This batch will fail with an error like this:

Msg 3930, Level 16, State 1, Line 7
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Books Online documents this behavior:

Uncommittable Transactions and XACT_STATE

If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION.

now replace TRY/Catch with the Test Method

BEGIN
BEGIN TRANSACTION t1;
SELECT 1

IF OBJECT_ID(N'tempdb.dbo.#Results', 'U') IS NOT NULL
BEGIN
DROP TABLE #Results;
END;

COMMIT TRANSACTION t1;
END

and run again.Transaction will commit without any error.

Cloning a table definition to a table variable in SQL Server

Create a user defined type with the columns of your table, lets say like that:

CREATE TYPE MyTableType AS TABLE (ID INT, Description VARCHAR(256));

And then declare your table variables using this type:

DECLARE @Table1 MyTableType;
DECLARE @Table2 MyTableType;
DECLARE @Table3 MyTableType;


Related Topics



Leave a reply



Submit