SQL Apprentice Question
Is there a function that can take an @Variable that will drop a table? I
can't seem to be able to get Drop Table @Variable to work because Drop Table
doesn't want a string, it wants the plain table name.
>> Is there a function that can take an @Variable that will drop a table? I can't seem to be able to get Drop Table @Variable to work because Drop Table doesn't want a string, it wants the plain table name. <<
Don't write code like this, nor use that mental model for RDBMS. A
table models a set of entities or relationships. Having tables appear
and disappear in an application is like a magic world in which
elephants drop from the sky or mountains dissolve in an instant.
Only the DBA should be creating or dropping tables and then only
**rarely** and with lots of documentation. The documentation is
important because the entire data model has just changed.
I'll bet what you have done is write a non-RDBMS in SQL that mimics a
magnetic tape file system, so you have a "create table on the fly"
routine in dynamic SQL (= "hang scratch tape") and now you want to drop
them (= "dismount scratch tape").
Wrong mental model of SQL programming!
The ANSI model for temporary table is that they are persisted like any
other base table, but they have two extra options: (1) They can clean
themselves at certain points in the session (2) they are either LOCAL
to a session or GLOBAL to everyone. This is very different from the
Sybase/SQL Server model, which is based on scratch tapes. YOu can
mimic this behavior easily in SQL Server.