Re: Using variables in Select Statements
- From: Dunner <Dunner@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 22 Jul 2005 01:06:01 -0700
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" wrote:
> 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)
>
.
- Follow-Ups:
- Re: Using variables in Select Statements
- From: Hugo Kornelis
- Re: Using variables in Select Statements
- References:
- Using variables in Select Statements
- From: Dunner
- Re: Using variables in Select Statements
- From: Hugo Kornelis
- Using variables in Select Statements
- Prev by Date: Re: Using variables in Select Statements
- Next by Date: Re: Using variables in Select Statements
- Previous by thread: Re: Using variables in Select Statements
- Next by thread: Re: Using variables in Select Statements
- Index(es):
Relevant Pages
|
|
