Re: SQLserver and the WHERE x IN y

From: ChrisRath (chrisrath_at_aol.com)
Date: 06/10/04 

Date: 10 Jun 2004 00:23:22 GMT

>Absolutely not! Think about the basics. Each table is a set of things
>of the same kind. If you could pass a table as a parameter:

My suggestion is not aimed at SQL itself, but rather T-SQL the procedural
language that is used in SQLServer. First things first, I am not talking about
an open ended architecture that would allow passing a generic table parameter
to a stored procedure - that would only work if you introduced subtyping into
the table definition mechanism (aka polymorphism). What I want is to be able
to pass a table variable of a known (and objectively defined) structure from a
calling procedure into a called procedure. A little background is in order.

First, T-SQL has two kinds of tables that fall outside of the framework that
you suggest. The oldest being temporary tables:

CREATE PROC MyProc AS
   CREATE TABLE #MyTempTable(MyLookupValue CHAR(1) NOT NULL)
   INSERT INTO #MyTempTable VALUES("A")
   INSERT INTO #MyTempTable VALUES("B")

   SELECT *
   FROM MyPermTable tab
   WHERE (tab.LookupValue IN(SELECT tbl.MyLookupValue FROM #MyTempTable tbl))

   DROP TABLE #MyTempTable
GO

Not an uncommon sort of construct for those who use T-SQL - temporary tables
have been quite handy over the years, allowing the use of SQL constructs on
temporary structures. The problem has been that passing a temporary table to
or from another procedure is a pain - thus limiting the ability to divide
procedures into smaller and more restricted modules. To do it now, you have to
play games using a dynamic sql execute statement to retrieve the contents of
the temp table from the calling procedure (the temp table is still in scope,
it's just not directly accessible as an entity).

In a similar vein, Temp tables can be created from the execution of another
stored procedure:

INSERT INTO #MyTempTable EXECUTE YourPopulateProc 123

The second kind of table is the table variable. These serve similar purposes
to the temporary tables with some added restrictions. The nicest aspect to
table variables is that they don't have transaction overhead - thus making them
faster. A table variable can be defined as:

CREATE PROC MyProc AS
   DECLARE TABLE @MyTableVar(MyLookupValue CHAR(1) NOT NULL)
   INSERT INTO @MyTableVar VALUES("A")
   INSERT INTO @MyTableVar VALUES("B")

   SELECT *
   FROM MyPermTable tab
   WHERE (tab.LookupValue IN(SELECT tbl.MyLookupValue FROM @MyTempTable tbl))
GO

The two biggest restrictions on Table Vars are (1). they are not accessible via
dynamic execute statements in the same manner as temp tables; and (2) they can
not be on the receiving end of a returned resultset from an executed stored
procedure.

Ok, that's where we are today. My suggestion is not about changing SQL or the
nature of tables as data structures. It is about taking table variables to the
next logical step. If I can create and manipulate table vars within a stored
procedure, what prevents me from passing a reference to these tables to other
stored procedures and functions? As long as the caller and callee agree on the
structure of the variable, why can't I simply pass the data? On the plus side,
it allows more modularity within stored procedures.

Just for grins, here's the way I'd define the stored procedure to receive a
table of a known type:

CREATE PROC MySubProcedure @MyTableVar TABLE(MyLookupValue CHAR(1) NOT NULL) AS
   INSERT INTO @MyTableVar VALUES("A")
   INSERT INTO @MyTableVar VALUES("B")
GO

This would allow me to populate and manipulate the table from subprocedures.
The actual creation and scope of the table is established by the top level
procedure. The subprocedures have everything they need to be able to resolve
the structure and relationships (as the structure and relationship has to be
defined in the procedure signature). Any proc that tried to pass in a table
that defies that structure should be reported as an error (and severely
taunted).

As for the larger question, I'm all for maintaining the integrity of the
relational database model. What I'm also for is establishing relational tables
that have a defined scope that is not just a permanent storage facility. One
can question the necessity of temporary tables or table variables, but once
brings them in the door, then one should allow them to be manipulated in the
same manner that one does for the permanent tables. Not allowing them to be
modularized between procedures and functions means that they behave in a
totally different manner than the perm tables.

Note, that this facility is available within PL/SQL though in a totally
different structure. You can have session recordsets, etc... that perform
similar facilities to the T-SQL above. In that language, you can cut the
procedures along the best lines without need to worry about taking the locally
defined tables (cursors, etc) out of scope. I can do everything I need with
temp tables and table vars, it's just that my procedures get to be much longer
than I'd like and they also have major sections that get repeated in a number
of different procedure.

(P.S. Enjoyed your book - sitting on my shelf at work).

Relevant Pages

  • Re: Error running Stored Procedures
    ... If ASP makes two separate calls to these stored procedures, ... temp tables only exist until all things that touch it go out of scope, ... have ASP call one stored procedure, ... > At line 43 is the command line to execute the recordset that will ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedure as part of SELECT?
    ... temp table variable and then execute a stored procedure that would populate ...
    (microsoft.public.sqlserver.programming)
  • procedure with temp tables
    ... I wrote a stored procedure which uses temp tables in its logic, ... DTS activex script, it is failing. ... If i remove temp tables usage inside ... procedure i am able to execute in the same activex script, ...
    (microsoft.public.sqlserver.dts)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • RE: SQL stored procedure executing twice
    ... caused the stored procedure to execute twice. ... from one parent record to another. ... Dim stDocName As String ... The table tempCount does not change until I execute the VBA code line: ...
    (microsoft.public.access.modulesdaovba)