dunnerr

Hi guys,

I was wondering if it is possible to use a variable in the from part of a
select statement as in...

select *
from @myVariable

Basically I am writing stored procedure that the user passes a table name to
and then it goes off and grabs info from this table and does some funky
stuff. Is it possible to make the statements semi-dynamic in that manner?

Cheers

hugo kornelis

Hi Dunner,

The short answer is: no this is not possible.

The slightly longer answer is: you can use dynamic SQL to kludge your
way around this limitation - lookup EXECUTE and sp_executesql in Books
Online if you must.

The long answer is that you should not want to do this. Each table will
have a structure that is different from other tables: the number of
columns, their names and datatypes, the keys and the other constraints
will all be different. I'm not sure how you propose to do "some funky
stuff" on rows without even knowing the number of the columns or their
names and datatypes, but I do hope that I'll never get to maintain the
nightmare.

In most cases, questions like yours are an indication of a design flaw.
If you can post some more information about your tables and your
business problem, then maybe I or others can propose a better design.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

dunnerr

Hi Hugo,

What I am trying to do is write a recursive query to find the dependencies
of a list of stored procedures (stored in one table) and place these in
another table. As the query goes it should place the name of each stored
procedure it has checked in a new table and then remove these from the
dependencies table to produce a list of dependencies which need to be checked
for their own dependencies (which is extremely confusing I know). This list
should then be checked so that you get the next level of dependencies, and
then the same procedure applied to the newly produced list which is again
checked. This would continue for up to say 3/4 levels of dependencies before
the procedure quits out - assuming there are still items to be checked. That
would be the somewhat funky stuff.

My initial thought was to call the procedure within itself passing it the
name of the dependencies table (initially it would be given the name of the
table containing the stored procedures to be checked). It could then run
happy as Larry for as many levels of dependencies as I see fit.

As such it's not strictly a business application and would never been seen
or used by end users because I just don't trust them with funk. It's merely
something I'm doing myself as part of a database tidying project. As I'm the
only one using it the tables in question have the same structure and it will
be quietly killed after I've finished the project so it doesn't really turn
into a maintenance problem.

That was just my initial idea though coming more from a C++ background and
only really getting into SQL in the last few weeks (I'm only a poor work
experience student ). I'll have another look and work out another method of
doing.

Cheers anyway for your help!

Dunner

hugo kornelis

Hi Dunner,

You could consider adding a column "DependencyLevel" (int, NOT NULL,
part of the compund primary key) to the work table. Don't insert new
dependencies in a new table, but in the existing work table with an
increased dependency level. Pass the dependency level as parameter to
your stored procedure instead of a table name. (snip)


In that case, you could also consider using dynamic SQL. But first, read
this page: http://www.sommarskog.se/dynamic_sql.html.

With your background, you'll have a lot of unlearning to do before you
can consider yourself to be truly good at SQL. C++ (as all other
algorithmic languagges) forces you to think of how the computer should
do things, looping over sets of data, processing one record at a time.
When using SQL, you'll have to change your mindset - you'll have to
learn to specify WHAT you want done, instead of HOW to do it. And you'll
have to specify that for all data at once, not on a row-by-row basis.
That will take time. But it'll be rewarding as well!

Good luck!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)