Trick SQL Server into accepting data sets as parameters
Unlike old-time cursor-based database systems, which work with single rows of data, modern relational database management systems (RDBMSs) such as SQL Server work with sets of data. However, SQL Server's set-oriented database engine doesn't let you directly send or receive data sets as parameters in interprocess communications such as triggers or stored procedure calls. SQL Server limits parameters to standard data types, and the ANSI SQL-92 standard doesn't have a data type for data sets (or recordsets, in ADO terminology). However, you can trick SQL Server into accepting data sets as parameters for interprocess communications by using a technique I call fake temporary tables.
Temporary tables let you store intermediate results or reuse results from previous queries in a complex, long-running report. Temporary tables also let you easily represent private data sets, which are available only to the session that created the table. However, you can't create temporary tables (or any tables, for that matter) in triggers except in SELECT INTO statements, and stored procedures can't accept temporary tables as parameters. But you can use fake temporary tables—permanent tables that emulate temporary tables—inside triggers and as parameters for stored procedures. Let's look at how to create fake temporary tables, then explore how you can use the tables inside triggers and in stored procedure calls.
About Temporary Tables
A temporary table, which SQL Server stores in its tempdb database, is a private set of rows and columns that belongs to one execution context. Because a temporary table belongs to a specific execution context (or connection), when someone closes that connection, every temporary table created in that connection disappears.
The names of local temporary tables—as opposed to global temporary tables, which I talk about later—begin with the pound (#) character. For example, executing the CREATE TABLE statement
CREATE TABLE #tmpA (
id INT,
value FLOAT)
GO
USE tempdb
GO
EXEC sp_help #tmpA
generates a temporary table with a name such as
#tmpA__. . .__000000000013
The numbers at the end of the name represent the table's unique relationship to the connection that created it. If another connection executes the same CREATE TABLE script, SQL Server creates a second #tmpA temporary table that has a different name suffix and that no one outside the second connection can see. The different temporary tables can even have different structures, although I recommend that you preserve a consistent table structure among processes to prevent maintenance problems.
A stored procedure that you call from inside a process can use temporary objects created in that process. You can even create within that stored procedure a temporary table with the same name as the original temporary table, and SQL Server will give the stored procedure's temporary table a unique name to avoid conflicts. Even better, SQL Server will automatically destroy the stored procedure's temporary table after the stored procedure finishes execution. However, you can't pass the stored procedure a temporary table or have the procedure return a temporary table to the calling procedure.
Global temporary tables also belong to a specific connection, but unlike local temporary tables, global tables are visible from other processes. The names of global temporary tables begin with two pound symbols (##). But SQL Server doesn't add a suffix to global temporary tables to identify the processes that created them, so trying to create the same global temporary table from another process produces a name conflict.
Many standard processes require you to join permanent tables to temporary tables. However, SQL Server always creates temporary tables in tempdb, which forces SQL Server to inefficiently cross database boundaries to join tables from different databases. But you can't create temporary tables outside of tempdb; so, what can you do to create a table outside tempdb to hold private, intermediate data sets?
Creating Fake Temporary Tables
Suppose you have a complex stored procedure that produces a long sales report. The report includes multiple sections, and your current stored procedure creates a temporary table, which the stored procedure reuses throughout its processing to optimize queries that produce other sections of the report. You want to avoid unnecessary joins to the original Products table, so you create a temporary table with all the necessary data. This table might have a structure such as
CREATE TABLE #SelectedProducts (
Prod_ID INT NOT NULL PRIMARY KEY,
Supplier_ID INT NOT NULL,
Description VARCHAR(200),
Price MONEY)
Prev. page
[1]
2
3
next page 
