However, what if multiple users need to simultaneously use the sales reporting process? In addition, you might need to call an external process, which would also need access to the temporary table. You might use OLE automation system stored procedures to call an external component, use the sp_start_job system stored procedure to execute a job, or use xp_cmdshell to call an external program. However, you can't send the temporary table to an external process, and sending just the name of the temporary table doesn't work because the unique name that SQL Server generates for the table is different from the one you used in the CREATE TABLE statement.
Instead of using the previous CREATE TABLE statement, you could create a permanent table, such as
CREATE TABLE fttSelectedProducts (
Inst_ID INT NOT NULL,
Prod_ID INT NOT NULL,
Supplier_ID INT NULL,
Description VARCHAR(200) NULL,
Price MONEY NULL,
CONSTRAINT PK_ftt_SelectedProducts
PRIMARY KEY NONCLUSTERED (Inst_ID, Prod_ID))
GO
/* Optional nonclustered index to optimize searching on Prod_ID */
CREATE NONCLUSTERED INDEX idxSelectedProducts_Prod_ID
ON fttSelectedProducts (Prod_ID)
This example creates a permanent table called fttSelectedProducts. But notice the new column Inst_ID, which identifies the execution context that you created the table in and which enables the permanent table to act like a local temporary table. Specifying NULL contents for every nonprimary key field is important if you want to easily insert dummy rows in this table, such as the initial row that you insert to block the Inst_ID value.
To generate a new Inst_ID value for every new execution of the CREATE statement, you can call a stored procedure such as GetInstID_SelectedProducts, which selects, locks, and returns a new unused value for the instance ID column. Listing 1 and Listing 2 show two examples of GetInstID_SelectedProducts. Listing 1's version of GetInstID_SelectedProducts uses the RAND() function, which works with any SQL Server release to generate random float values for Inst_ID. Listing 2's version uses the NEWID() function, which Microsoft introduced with SQL Server 7.0, to assign a unique Inst_ID value of type uniqueidentifier. Although the NEWID() solution is simpler and easier to maintain than the RAND() solution, the NEWID() version uses 16 bytes to store the uniqueidentifier field value instead of the 4 bytes that RAND() uses to store its integer field. Later, I cover more performance considerations that you should explore before deciding whether to use RAND() or NEWID().
When you have a way to generate unique Inst_ID values, you can use the appropriate subset of fttSelectedProducts as if it were a temporary table. Just include WHERE Inst_ID = @Inst_ID to limit the results to the appropriate private data set. To call a stored procedure or an external utility to process the data set, you need to pass the procedure or utility only one parameter: the value of @Inst_ID. Just make sure that no process uses the dummy entry (in this example, Prod_ID=0), which this example uses only to block the new Inst_ID value.
When you no longer need the data in the fake temporary table, just use a stored procedure or a simple DELETE statement to remove the data set:
DELETE FROM fttSelectedProducts
WHERE Inst_ID = @Inst_ID
If you forget to remove the data set, your fake temporary table will contain wasted space. To guard against this wasted space, you can create an autostart job to clean out the table every time SQL Server starts. Or, you can define a job that tests whether the table is in use and truncates the table if it isn't.
Now that you understand how to create fake temporary tables, you can use them to solve real-world problems. Let's explore two examples: how to use fake temporary tables inside triggers and how to use fake temporary tables to execute stored procedures affecting a batch of changes. (Also, for implications of using this technique with SQL Server 2000, see the sidebar "Fake Temporary Tables and SQL Server 2000.")
Using Fake Temporary Tables Inside Triggers
Suppose you want to call extended stored procedure xp_sendmail from a trigger to send managers a message that contains information about modified salary rows in the Employees table. You can't read Inserted or Deleted tables from xp_sendmail because these virtual tables are private to the execution of the trigger, and you can't create a temporary table inside the trigger. However, you can use a permanent table inside the trigger to hold information about the modified rows. And a fake temporary table is nothing more than a permanent table that holds information private to a specific execution context. Inside the trigger, you need to simply call a stored procedure to generate a new random value that identifies the execution context of the trigger. Note that writing too much application logic inside triggers isn't safe or efficient; you're better off storing such logic in stored procedures. The following example demonstrates how to select a data set from a trigger, then send the data set to another procedure.
Prev. page
1
[2]
3
next page 
