Parameterize FROM clause in Stored Procedure: How To?

[billmaclean1@gmail.com]

I need to write a stored procedure that selects from a table and
returns the result set. I don't always know the TableSchema that I
need to use when qualifying the table at run-time

Example: The correct table could either be dbo.MyTable or
zzz.MyTable.

I want the user to enter the name of the schema as a parameter of the
procedure at run-time.

However, SQL Server gives me an error when I try create the procedure
using the parameter in the FROM clause.

Can anyone give me a hand?

Thanks,

Bill

Run the scripts below to see my problem:

/* Make the sample table */
create table MyTable (TabKey int NOT NULL);
alter table MyTable add constraint MyTable_PK primary key (TabKey);

/* insert sample values */
insert into MyTable values (1);
insert into MyTable values (2);
insert into MyTable values (3);

/*This statement works fine. (Notice, I don't make use of @TableSchema
just declare it to prove there is
no syntax error in the declaration */
CREATE PROCEDURE TestProc (@TableSchema varchar(80)) AS
BEGIN
SELECT * from dbo.MyTable
END;

/* Run the Procedure (Doesn't matter what I put for the parameter) */
TestProc 'dbo'

/* Drop Procedure */
drop procedure TestProc

/* Try to Re-create the procedure with a parameterized FROM clause
that uses @TableSchema, but get an error */
CREATE PROCEDURE TestProc (@TableSchema varchar(80)) AS
BEGIN
SELECT * from @TableSchema.My Table ---- <<<< This causes the
error
END

/*
Here is the error message:
Msg 156, Level 15, State 1, Procedure TestProc, Line 5
Incorrect syntax near the keyword 'END'.
*/

[Dan Guzman]

Re: Parameterize FROM clause in Stored Procedure: How To?

I want the user to enter the name of the schema as a parameter of the

procedure at run-time.

Is this requirement because you have many identical tables with the same
structure but different data? IMHO, a better approach in this case might be
to add a partitioning column and use a single table. See Erland's article
for a thorough discussion of dynamic SQL considerations:

http://www.sommarskog.se/dynamic_sql.html

Note that it's a good practice to avoid SELECT * in production code and
instead specify an explicit column list. A stored procedure interface
(parameters and resultset) should be well-defined and not dependent on the
columns that happen to be in the table that day.

--
Hope this helps.

Dan Guzman
SQL Server MVP

http://weblogs.sqlteam.com/dang/

<billmaclean1@g mail.comwrote in message
news:91ae8444-307f-403f-9b46-e926f4965b1f@u2 7g2000pro.googl egroups.com...

>I need to write a stored procedure that selects from a table and
returns the result set. I don't always know the TableSchema that I
need to use when qualifying the table at run-time
>
Example: The correct table could either be dbo.MyTable or
zzz.MyTable.
>
I want the user to enter the name of the schema as a parameter of the
procedure at run-time.
>
However, SQL Server gives me an error when I try create the procedure
using the parameter in the FROM clause.
>
Can anyone give me a hand?
>
Thanks,
>
Bill
>
Run the scripts below to see my problem:
>
/* Make the sample table */
create table MyTable (TabKey int NOT NULL);
alter table MyTable add constraint MyTable_PK primary key (TabKey);
>
/* insert sample values */
insert into MyTable values (1);
insert into MyTable values (2);
insert into MyTable values (3);
>
/*This statement works fine. (Notice, I don't make use of @TableSchema
just declare it to prove there is
no syntax error in the declaration */
CREATE PROCEDURE TestProc (@TableSchema varchar(80)) AS
BEGIN
SELECT * from dbo.MyTable
END;
>
/* Run the Procedure (Doesn't matter what I put for the parameter) */
TestProc 'dbo'
>
/* Drop Procedure */
drop procedure TestProc
>
/* Try to Re-create the procedure with a parameterized FROM clause
that uses @TableSchema, but get an error */
CREATE PROCEDURE TestProc (@TableSchema varchar(80)) AS
BEGIN
SELECT * from @TableSchema.My Table ---- <<<< This causes the
error
END
>
/*
Here is the error message:
Msg 156, Level 15, State 1, Procedure TestProc, Line 5
Incorrect syntax near the keyword 'END'.
*/

[bill]

Re: Parameterize FROM clause in Stored Procedure: How To?

Hi Dan,

RE: Single table with a partitioning column. I agree that this would
be a good solution, but it may not be possible for me to control this
particular situation.

RE: SELECT *. I agree and would never use it in production. I just
used it in the example scripts for economy of typing.

This leaves me still needing to create a procedure with a
parameterized FROM clause. Any ideas on how to do that?

Thanks,

Bill

On Oct 17, 3:34 am, "Dan Guzman" <guzma...@nospa m-
online.sbcgloba l.netwrote:

I want the user to enter the name of the schema as a parameter of the
procedure at run-time.

>
Is this requirement because you have many identical tables with the same
structure but different data?  IMHO, a better approach in this case might be
to add a partitioning column and use a single table.  See Erland's article
for a thorough discussion of dynamic SQL considerations:http://www.sommarskog.se/dynamic_sql.html
>
Note that it's a good practice to avoid SELECT * in production code and
instead specify an explicit column list.  A stored procedure interface
(parameters and resultset) should be well-defined and not dependent on the
columns that happen to be in the table that day.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVPhttp://weblogs.sqlteam .com/dang/

[bill]

Re: Parameterize FROM clause in Stored Procedure: How To?

Hi Dan,

I've been thinking more about this, because I it seems kind of cheesy
to pass a parameter into the FROM clause. In fact, wouldn't doing
that essentially turn my procedure into dynamic SQL, and trash the
advantage of a stored plan?

So here's my thought: I can't do anything about the multiple tables
in different schemas (that issue is beyond my control). However, I do
know which schemas might contain a version of the table that I need to
query

Could I just build a view in a known schema that Unions all the
possible tables I need to query (these are small tables)? I would
put a literal in each of the Unioned SELECTs that identifies the
TableSchema. Like this:

CREATE VIEW crap as
SELECT 'dbo' TableSchema, TabKey
FROM dbo.Mytable
UNION ALL
SELECT 'yyy' TableSchema, TabKey
FROM yyy.Mytable

Now I have a single view to query that contains all my data, plus a
column that tells what schema holds the base table. My procedure can
just do a WHERE TableSchema = @TableSchema to query the correct
table. This gets me prettty close to your ideal single table with a
partioning column, considering that I cannot eliminate the base tables
in multiple schemas.

What do you think?

Thanks,

Bill

[steve]

Re: Parameterize FROM clause in Stored Procedure: How To?

On Oct 17, 12:14 pm, bill <billmacle...@g mail.comwrote:

Hi Dan,
>
I've been thinking more about this, because I it seems kind of cheesy
to pass a parameter into the FROM clause.  
.

The most fundamental idea of a "relational " database is that a table
is a VARIABLE
and can be passed as a parameter just like a variable defined as an
integer. The concept of a table as a variable does not exist in sql
and users are left to fumble
with dynamic sql as a way to compensate for the lack. Only in an
upside down world could a table as a variable be seen as 'cheesy'. And
if sql is a cheese it must be swiss :)

http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html

http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html

https://www.beyondsql.blogspot.com

[Erland Sommarskog]

Re: Parameterize FROM clause in Stored Procedure: How To?

bill (billmaclean1@g mail.com) writes:

I've been thinking more about this, because I it seems kind of cheesy
to pass a parameter into the FROM clause. In fact, wouldn't doing
that essentially turn my procedure into dynamic SQL, and trash the
advantage of a stored plan?

Each table which require its own query plan, one way or another.
Of course, if you select all rows the plan is trivial. But say
that there is a WHERE clause with a condition on a column with a
non-clustered index. For one table it could be right to use that
index, but for another table, a scan is a better choice.

There is no way you can put in a parameter directly, but you can of
course use dynamic SQL. Or you could have a bunch of IF statements.

So here's my thought: I can't do anything about the multiple tables
in different schemas (that issue is beyond my control).

But isn't there someone you can go and beat up about this issue? :-)
I mean, if this is a poor design, someone should be told.

Could I just build a view in a known schema that Unions all the
possible tables I need to query (these are small tables)? I would
put a literal in each of the Unioned SELECTs that identifies the
TableSchema. Like this:
>
CREATE VIEW crap as
SELECT 'dbo' TableSchema, TabKey
FROM dbo.Mytable
UNION ALL
SELECT 'yyy' TableSchema, TabKey
FROM yyy.Mytable
>
Now I have a single view to query that contains all my data, plus a
column that tells what schema holds the base table. My procedure can
just do a WHERE TableSchema = @TableSchema to query the correct
table. This gets me prettty close to your ideal single table with a
partioning column, considering that I cannot eliminate the base tables
in multiple schemas.

Yes, that is a very good approach. You should check the query plans,
though, so that SQL Server includes some startup filter, and do not
read all rows from all tables before filtering. This mainly an issue
if the table are large in size.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

Re: Parameterize FROM clause in Stored Procedure: How To?

Could I just build a view in a known schema that Unions all the

possible tables I need to query (these are small tables)? I would
put a literal in each of the Unioned SELECTs that identifies the
TableSchema. Like this:
>
CREATE VIEW crap as
SELECT 'dbo' TableSchema, TabKey
FROM dbo.Mytable
UNION ALL
SELECT 'yyy' TableSchema, TabKey
FROM yyy.Mytable

Very creative! I agree with Erland that this is a good idea for your
situation.

--
Hope this helps.

Dan Guzman
SQL Server MVP

http://weblogs.sqlteam.com/dang/

"bill" <billmaclean1@g mail.comwrote in message
news:1a4b547f-6fe1-4790-b0a3-746c1f446c23@31 g2000prz.google groups.com...

Hi Dan,
>
I've been thinking more about this, because I it seems kind of cheesy
to pass a parameter into the FROM clause. In fact, wouldn't doing
that essentially turn my procedure into dynamic SQL, and trash the
advantage of a stored plan?
>
So here's my thought: I can't do anything about the multiple tables
in different schemas (that issue is beyond my control). However, I do
know which schemas might contain a version of the table that I need to
query
>
Could I just build a view in a known schema that Unions all the
possible tables I need to query (these are small tables)? I would
put a literal in each of the Unioned SELECTs that identifies the
TableSchema. Like this:
>
CREATE VIEW crap as
SELECT 'dbo' TableSchema, TabKey
FROM dbo.Mytable
UNION ALL
SELECT 'yyy' TableSchema, TabKey
FROM yyy.Mytable
>
Now I have a single view to query that contains all my data, plus a
column that tells what schema holds the base table. My procedure can
just do a WHERE TableSchema = @TableSchema to query the correct
table. This gets me prettty close to your ideal single table with a
partioning column, considering that I cannot eliminate the base tables
in multiple schemas.
>
What do you think?
>
Thanks,
>
Bill

[--CELKO--]

Re: Parameterize FROM clause in Stored Procedure: How To?

>I need to write a stored procedure that selects from a table and returnsthe result set.  I don't always know the TableSchema that I need to use when qualifying the table at run-time <<

This kind of magical thinking is called "Automobile s, Squids and
Britney Spears" programming.

>Example: The correct table could either be dbo.MyTable [aka "automobile s] or zzz.MyTable {aka Squids]. <<

A table models one and only one kind of entity or relationship.
Likewise, a schema models one and only one Universe of Discourse. You
are supposed to know what it is before you write code against it.
This is the concept of cohesion that you should have had beaten into
your head in Software Engineering 101.

>Can anyone give me a hand? <<

Any of the classics in Software Engineering will help you (Yourdon,
DeMarco, Constantine, Myers, et al) ; then you can learn SQL and
declarative programming with a foundation.

[bill]

Re: Parameterize FROM clause in Stored Procedure: How To?

Hi Joe,

You are making a number of unwarranted assumptions when you claim to
understand my thinking process or what I know and don't know. I'm a
big advocate of set based operations and keeping procedural code to an
absolute minimum. I agree that there _ought_ to be only one table,
but the facts beyond my control dictate that there _is_ more than one
table.

No matter how distasteful, I have to work with the multiple tables.
To correct mysellf, I don't _have_ to work with the the one table, but
the alternative of simply refusing to do the work because of a sub-
optimal schema is unattractive to me.

My idea of the parameterized FROM clause wasn't one of my best, but I
was looking for a solution. Dan Guzman did a nice job of reminding me
(and, amazingly, he didn't even have to be scornful to be effective!)
what I really wanted, which was a single table. Since a single table
isn't a possibility, a UNIONed view is the next best thing.

Remember, the multiple-schema union compatible tables are delivered as
part of a delivered commercial product over which I have no control.

Do you suggest that I trash the view and simply refuse to do the work?

Thanks,

Bill



On Oct 19, 1:45 pm, --CELKO-- <jcelko...@eart hlink.netwrote:

I need to write a stored procedure that selects from a table and returns the result set.  I don't always know the TableSchema that I need to use when qualifying the table at run-time <<

>
This kind of magical thinking is called "Automobile s, Squids and
Britney Spears" programming.
>

Example: The correct table could either be dbo.MyTable [aka "automobile s] or zzz.MyTable {aka Squids]. <<

>
A table models one and only one kind of entity or relationship.
Likewise, a schema models one and only one Universe of Discourse. You
are supposed to know what it is before you write code against it.
This is the concept of cohesion that you should have had beaten into
your head in Software Engineering 101.
>

Can anyone give me a hand? <<

>
Any of the classics in Software Engineering will help you (Yourdon,
DeMarco, Constantine, Myers, et al) ; then you can learn SQL and
declarative programming with a foundation.

[steve]

Re: Parameterize FROM clause in Stored Procedure: How To?

Comparing the computer science of sql to relational is liking
comparing the nervous system of an amoeba to a human being, even an
sql expert. In the static and variable-less world of sql you get
hammered with the idea that a thing, like a procedure, can only be
applicable to a another single thing. Relationally, a procedure is
every bit a variable just like a table. As a variable a procedure can
manifest any supported type like an integer (a scalar value) ,a row
(yes it's a real type), or a table. And to think that a 'variable' can
only be a single value is to not think at all. Given:
declare @X int
set @X=4
if someone said that @X could only take the integer 4 that is an sql
idea and a
damn crazy one at that. But this is the sql thinking about the nature
of a procedure
that realizes (or attempts to realize) a table. Sql'ers who can't wrap
their head around the concept of a variable as it relates to tables,
procedures and data should carefully read articles like:

http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html

http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html

http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html

http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-server-part-i.html

And in some cases reread them.

Now sql is a nice niche scripting language. The problem is it's thrust
down the throat
of application developers where it really has no business. Application
development
without variables. Now how crazy-foolish is that? It may be necessary
to redefine
the Q in SQL, S(pecial) Q(uarantined) L(anguage). We really don't need
any more
generations of developers having their minds warped. Med students
study the central
nervous system not some tiny microscopic thing served up in a petri
dish from before
the dawn of time.

https://www.beyondsql.blogspot.com

[Serge Rielau]

Re: Parameterize FROM clause in Stored Procedure: How To?

Faszinating thread.
Disclaimer: The following has nothing to do with the OP's real world
issue that he has no control over, but is meant to steer against the
polarized views been flushed up here....

The S in SQL stands for structure. Making just about everything in a
query a variable is not structure. It is the ultimate of freedom (and risk).
This is nothing new. Lisp for example has provided similar freedom for
many decades and I do recall my self modifying Z80 assembler code with
fondness and shudder....but. . back to the topic.

A DBMS is meant to be finely tuned to the application and that implies
that the interactions are largely well known. That way indexes can be
created, table designs chosen, etc.
Dynamically composed SQL (and let's not kid ourselves: table variables
are nothing other than dynamic SQL in a different skin) allow for a lot
of curve balls to be thrown at the system resulting in quite
unpredictable outcomes w.r.t. service level agreements.
E.g. statement caching becomes a real challenge and so does problem
determination.

In other words SQL is what it is because the experts in relational DBMS
figured that is the proper and performant way to do it.
Anyone who wants something different is free to do it their way (XQuery
anyone? Map Reduce? Streaming?)

Instead of pointing to MS SQL Server Blog articles I'd point to academic
papers if I wanted to make a scientific point btw.... (I certainly
wouldn't point to any of my DB2 articles to argue language theory...).
And just because something is possible (tables as parameters,
associative arrays, ...) doesn't mean it's the best tool.

Cheers
Serge
--
Serge Rielau
SQL Architect, DB2 for LUW
IBM Toronto Lab

[--CELKO--]

Re: Parameterize FROM clause in Stored Procedure: How To?

>Remember, the multiple-schema union compatible tables are delivered as part of a delivered commercial product over which I have no control. <<

Why would I remember something I was never told? I earn a lot of my
living doing product reviews and you would be surprised just how awful
the insides of commercial product are.

>Do you suggest that I trash the view and simply refuse to do the work? <<

The UNION-ed VIEW is a good kludge for this kind of crap. Have you
contacted the provider? Have you looked at another product? Have you
computed the cost of a home-made solution?

[bill]

Re: Parameterize FROM clause in Stored Procedure: How To?

To your questions:

1. (a) I should not have used the word 'remember', as I did not
explicitly state that the tabels were part of a commercial product nor
that they were union compatible. However, had you not been so keen to
jump on me for what you believed was my 'magical thinking' and your
assumption that I do not understand SQL, you might have inferred from
my earlier messages the information that I didn't explicitly state.

In my original reply to Dan Guzman I agreed that a single table
solution would be best, but said it "may not be possible for me to
control this particular situation", a reference (admittedly rather
oblique) to the constraints I face in dealing with a commercial
product.

In my next reply to Dan, I proposed the UNIONed view, and even threw
in a little SQL psuedocode to illustrate my idea. That psuedocode
only showed a UNION of primary keys, but it reasonably indicated that
the tables were union-compatible, so I don't know how the
'Automobiles, Squids and Britney Spears' line was relevant.

I know that you have a lot of good ideas (For example, I particularly
like your explanation of nested sets for storing and managing trees)
and that it can be frustrating to see people attempt to use code for
tasks that are ideally suited to set operations and the database
engine It appears that you assumed the worst. Even if your
assumptions had been correct, I think the tone of your reply was not
particularly productive.

1(b). Actually, I would not be surprised by the sorry state of schema
designs in commercial products. I have been exposed to many different
commercial products, from "nearly home grown", to products from very
large compaines with very high market penetration. Most of them
unfortunately have very bad schemas. For many of these products,
deceptively say they "use a relational database". A more accurate
description would be that the they "use a relational-capable engine as
a file storage system. It's definitely a bad problem.

2) All good suggestions, and the answer to each is 'yes'. The upshot
is that we''re stuck with what we have for the mid term at least, so I
think the view is the best solution.

Thanks,

Bill

On Oct 20, 8:02 am, --CELKO-- <jcelko...@eart hlink.netwrote:

Remember, the multiple-schema union compatible tables are delivered aspart of a delivered commercial product over which I have no control. <<

>
Why would I remember something I was never told?  I earn a lot of my
living doing product reviews and you would be surprised just how awful
the insides of commercial product are.
>

Do you suggest that I trash the view and simply refuse to do the work?<<

>
The UNION-ed VIEW is a good kludge for this kind of crap.  Have you
contacted the provider?  Have you looked at another product?  Have you
computed the cost of a home-made solution?

[--CELKO--]

Re: Parameterize FROM clause in Stored Procedure: How To?

> For many of these products, deceptively say they "use a relational database". A more accurate description would be that the they "use a relational-capable engine as
a file storage system. It's definitely a bad problem. <<

I had a friend back in the 1970's who worked for MSA when they were
around, She was the "Database Gal" and her job was to directly port
the COBOL files into IDMS, TOTAL and several other early network DBs.
They did not want to re-write their products, but wanted to claim a
real cool, real hip DB.

> the answer to each is 'yes'. The upshot is that we''re stuck with what we have for the mid term at least, so I think the view is the best solution. <<

I'd go with that, too. If they fix the schema, you can do a quick
edit and drop the VIEW for a real base table.

[Roy Harvey (SQL Server MVP)]

Re: Parameterize FROM clause in Stored Procedure: How To?

On Tue, 21 Oct 2008 17:09:51 -0700 (PDT), --CELKO--
<jcelko212@eart hlink.netwrote:

>> For many of these products, deceptively say they "use a relational database". A more accurate description would be that the they "use a relational-capable engine as

>a file storage system. It's definitely a bad problem. <<
>
>I had a friend back in the 1970's who worked for MSA when they were
>around, She was the "Database Gal" and her job was to directly port
>the COBOL files into IDMS, TOTAL and several other early network DBs.
>They did not want to re-write their products, but wanted to claim a
>real cool, real hip DB.

In the late 1990's the company I was working chose JD Edwards running
on the IMB AS/400 as the package to run the enterprise. The AS/400
was always touted as being a "database machine" where "all the data is
in databases". And you could query anything using DB/2. However,
among the hundreds, perhaps thousands, of "tables" in that system I
could not find a single unique constraint of any kind. Not a single
unique key, anywhere. Of course the packaged application did not have
a line of SQL either, as it only used the give-me-the-next-record
capability that was the norm for the platform. When JD Edwards
upgraded the package to the next generation they started offering it
on other databases and experienced all sorts of problems with data
conversion. That didn't surprise me since I knew from painful
experience that it was not possible to find a key for an invoice line
item.

Roy Harvey
Beacon Falls, CT