Parameterize FROM clause in Stored Procedure: How To?

billmaclean1

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.MyTable ---- <<<< 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'.
*/

Oct 17 '08 #1

Dan Guzman

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/

<bi**********@gmail.comwrote in message
news:91**********************************@u27g2000 pro.googlegroups.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.MyTable ---- <<<< 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'.
*/

Oct 17 '08 #2

bill

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...@nospam-
online.sbcglobal.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/

Oct 17 '08 #3

bill

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

Oct 17 '08 #4

steve

On Oct 17, 12:14*pm, bill <billmacle...@gmail.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/0...parameter.html
http://beyondsql.blogspot.com/2007/0...variables.html

www.beyondsql.blogspot.com

Oct 18 '08 #5

Erland Sommarskog

bill (bi**********@gmail.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, es****@sommarskog.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

Oct 18 '08 #6

Dan Guzman

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" <bi**********@gmail.comwrote in message
news:1a**********************************@31g2000p rz.googlegroups.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

Oct 19 '08 #7

--CELKO--

>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 "Automobiles, Squids and
Britney Spears" programming.

>Example: The correct table could either be dbo.MyTable [aka "automobiles] 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.

Oct 19 '08 #8

bill

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...@earthlink.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 "Automobiles, Squids and
Britney Spears" programming.

Example: The correct table could either be dbo.MyTable [aka "automobiles] 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.

Oct 19 '08 #9

steve

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/0...variables.html
http://beyondsql.blogspot.com/2007/0...parameter.html
http://beyondsql.blogspot.com/2007/0...-function.html
http://beyondsql.blogspot.com/2007/1...er-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.

www.beyondsql.blogspot.com

Oct 20 '08 #10

Serge Rielau

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

Oct 20 '08 #11

--CELKO--

>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?

Oct 20 '08 #12

bill

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...@earthlink.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?

Oct 20 '08 #13

--CELKO--

> 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.

Oct 22 '08 #14

Roy Harvey (SQL Server MVP)

On Tue, 21 Oct 2008 17:09:51 -0700 (PDT), --CELKO--
<jc*******@earthlink.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

Oct 22 '08 #15

Ed Murphy

steve wrote:

On Oct 17, 12:14 pm, bill <billmacle...@gmail.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/0...parameter.html
http://beyondsql.blogspot.com/2007/0...variables.html

www.beyondsql.blogspot.com

The specific situation at hand here is akin to having two tables in
the database called MaleEmployees and FemaleEmployees. You /could/
use table-type variables, but what you really should do instead is
merge both into one table (or view, if you can only fix things one
piece at a time) with a Gender column added.

As previously discussed, I agree that table-type variables would be
useful in other situations, though I still think you're shooting
yourself in the foot by constantly pointing to an instance (Dataphor)
that would incur a huge switchover cost for existing projects. SQL
Server 2008 has table-valued parameters, albeit limited to read-only
input parameters; within that limit, do you approve of them?

Oct 22 '08 #16

steve

On Oct 19, 7:24*pm, Serge Rielau <srie...@ca.ibm.comwrote:

>Faszinating thread.

I should think we could use many more :)

>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....

I hope your not using 'polarizing' pejoratively. Lets eliminate as
much doubt as we can in the readers mind as to what we mean. Polarized
views should be healthy. We built a judicial system upon it. And after
all I'm sure we both agree we wouldn't kill each other:)

>The S in SQL stands for structure.

To many the 'S(tructure)' is the declarative nature of the language.
And given the keywords of the language used declaratively this equates
to relational. It does not.

>Making just about everything in a query a variable is not structure.

Another polarizing view:) A rose is a rose is a rose. A variable is a
variable is a variable. You seem to feel that something is lost when
you introduce the idea of a table as a variable and a query itself as
a variable. What is lost? It's more a question of what is gained.
Would a user think twice about an integer expression with variables?
The question of 'losing' something with integer variables in an
expression (as opposed to constants) would never occur to someone (I
hope). The relational idea is simply to transition users with the same
mindset using integers and strings to expressions using tables. Put
even more simply it is to introduce assignment with tables and you
can't talk about assignment without variables. Sql has managed to
define itself independent of a computer science known to developers. A
relational db is putting basic computer science back in the database:)

>It is the ultimate of freedom (and risk).

What is the nature of the 'risk' you see? Besides, given the level of
abuse of sql in IT today could the introduction of new ideas possibly
raise it significantly? When it comes to risk we have nowhere to go
but down:)

>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.

How relational ideas would somehow undermine things in beyond me.
Today we have dbs that can do everything with an index and virtually
nothing with a key. Does
a SELECT statement have anything to do with the concept of a key? Do
we want
b-tree experts or application developers? Today there are only
performance hints. Do
you think users will object to 'logical' hints?

>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.

Do you see something 'dynamic' as a consequence of implementing a
relational (as I'm advocating it) db? I don't really know where you're
coming from here:(:) I do know that from the relational viewpoint
dynamic sql is the sql attempt to work with a table as if were
relationally realized. It's the sql workaround to simulate a variable
from a static structure. Just like relational division (the ability to
'directly' compare tables) is simulated in sql with aggregate
functions.

>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?)

Well Alan Greenspan convinced everyone derivatives and non-regulation
were good for the economy:(:) I'm actually less interested in tearing
down sql than creating room for something else for application
development. But sql holds such a monopoly in IT that it's hard to
create an opening for something that looks similar but is very
different. Sql has co-opted the broad ideas of a relation database
making it appear to most users as if they are one and the same. The
great deception of "relational-like". So separating the two remains a
moving target:)

>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.

Criticizing is easy, being a critic is difficult. Most users have
deprecated scholarship much like a db deprecates certain features. The
industry has historically succeeded in marginalizing sql critics, even
the truly scholarly ones. Such inconsequential criticism comes from
relational purists, theorists and academics divorced from the 'real'
world of IT. Most evangalists from vendors, with vested interests and
turf at stake, trivialize criticism regardless of the shamefulness of
some their arguments (or excuses). On the other hand many critics of
sql do not fall short of foolishness.
The ad hominem attackers and the 'throw it all out' crowd. The idea
that the enormous amount of scholarship contributed by so many in the
development of sql dbs should somehow be dismissed is stupid and
absurd. In view of such a polarized environment and given the track
record of the various sides why not point to my own articles as a
point of reference:) Given it's still very much a QBE world why not
toot my own horn:) I'm arguing less theory than illustrating
practicality. Perhaps it takes something more (or less) than
'scholarship' to open the door and interest users to
something other than sql as the foundation for application
development.

best,
steve
www.beyondsql.blogspot.com

Oct 22 '08 #17

BIGsql

I wonder if a UDF would be any use here?

Oct 24 '08 #18

BIGsql

Or even, horrors, build up the SQL as a varchar and exec() it?

Oct 24 '08 #19

John Smith

Refer to my example below...

-------------------------------------------------
-- By: Ron Santillano --
-- July 3, 2008 --
-------------------------------------------------
-- Anonymous Procedure will create a temporary --
-- table and store record couns on the current --
-- database. --
-------------------------------------------------
Declare
@Name Varchar(100),
@Command nVarchar(200),
@Rec_Counts Integer

Declare @cSource Cursor;
Set @cSource = Cursor Fast_Forward For
Select Name
From Sysobjects
Where Xtype = 'U'

Begin
--truncate table ##record_counts
Create Table ##Record_Counts (
TableName Varchar(100),
RecordCounts Integer)

Open @cSource;
Fetch Next From @cSource Into
@Name

While @@FETCH_STATUS = 0
Begin
Set @Command = N'Select @Rec_Counts = Count(1) From ' + @Name
EXEC sp_executesql
@query = @Command,
@params = N'@Rec_Counts INT OUTPUT',
@Rec_Counts = @Rec_Counts OUTPUT
Insert Into ##Record_Counts ( TableName, RecordCounts)
Values (@Name, @Rec_Counts)
Fetch Next From @cSource Into
@Name

End --while

Close @cSource
Deallocate @cSource

End
<bi**********@gmail.comwrote in message
news:91**********************************@u27g2000 pro.googlegroups.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.MyTable ---- <<<< 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'.
*/

Nov 2 '08 #20

bill

I was trying to avoid building up the SQL string. I have a natural
bias against this method, because you don't get any of the benefits of
re-used plans. Of course, someone already correctly pointed out that
my bias should not apply in this case, because if you're going against
diferent tables, you're gonna have different plans. I still like the
view, because it keeps the procedure very simple, and if the vendor
every combines the tables (like they should), I can just dump the view
and make minor changes to the procedure.

Thanks,

Bill

On Oct 24, 6:05*am, BIGsql <michael_bar...@hotmail.comwrote:

Or even, horrors, build up the SQL as a varchar and exec() it?

Nov 3 '08 #21