Parameterize FROM clause in Stored Procedure: How To?

[Ed Murphy]

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

steve wrote:

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

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

[steve]

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

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
>unpredictabl e outcomes w.r.t. service level agreements.
>E.g. statement caching becomes a real challenge and so does problem
>determinatio n.

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

https://www.beyondsql.blogspot.com

[BIGsql]

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

I wonder if a UDF would be any use here?

[BIGsql]

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

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

[John Smith]

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

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


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

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.comwro te:

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