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
Getting the Number of Rows with a Group by Query
How to Select Similar Sets in SQL
Group by and Count in Postgresql
Is MySQL Limit Applied Before or After Order By
Oracle SQL: Use Sequence in Insert with Select Statement
How to Select Rows Having Column Value as Null
Bigquery SQL for Sliding Window Aggregate
How to Preview a Destructive SQL Query
Finding Rows with Same Values in Multiple Columns
How to Load SQL Fixture in Django for User Model
Keep Only N Last Records in SQLite Database, Sorted by Date
Get List of Computed Columns in Database Table (SQL Server)