Re: Using variables in Select Statements
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 22 Jul 2005 19:56:19 +0200
On Fri, 22 Jul 2005 01:06:01 -0700, Dunner wrote:
>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.
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)
> 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.
In that case, you could also consider using dynamic SQL. But first, read
this page: http://www.sommarskog.se/dynamic_sql.html.
>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.
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)
.
- References:
- Using variables in Select Statements
- From: Dunner
- Re: Using variables in Select Statements
- From: Hugo Kornelis
- Re: Using variables in Select Statements
- From: Dunner
- Using variables in Select Statements
- Prev by Date: Re: Using variables in Select Statements
- Next by Date: Exist Return Values
- Previous by thread: Re: Using variables in Select Statements
- Next by thread: Exist Return Values
- Index(es):
Relevant Pages
|
|
