|
|
Posted by Dip on 09/27/06 02:35
Thanks Erland,
I actually printed out your article and went through it. It is actually
very well written and covers all general situations, however, I
didn't have much luck constructing the Dynamic SQL to generate
"possible" columns and add each "LEFT OUTER JOIN" for each
level. Even if I break it down to two SQL Text, I would still need to
tell it to add 10 columns for each level for example and 9 LEFT OUTER
JOINs to break the Parent Child Adjacent model if WBS_LEVEL is 10 for
instance.
I have designed Stored Proc with Dynamic SQL in it but I haven't done
anything like this one before. Either it is silly simple or I just
can't get my head around to it.
I don't think any literature would help me to solve this problem but
some actual code that relates to this issue.
Thanks for all help.
Soumya
Erland Sommarskog wrote:
> Dip (soumyadip.bhattacharya@gmail.com) writes:
> > WBS_LEVEL would be, in this situation, 5 but it could go for any number
> > in future when all divisions would start using Project Module. They can
> > have any depth of tasks allocated for a project.
> > To me, it's appearing a bit more complex than I initially thought. How
> > do I construct the self joins for each level dynamically?
> > Has anyone had done this before? Any sample code is available suitable
> > to this scenario?
>
> Did you even look at the article I posted the link to?
>
> What you need to do is:
> 1) Get the current MAX value of WBS_LEVEL from Projects.
> 2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
> "FROM dbo.Project t1".
> 3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
> join condition to respective strings.
> 4) Execute the SQL string.
>
> It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
> are full of samples with dynamic SQL, even if not for this precise problem.
> (The most reason there are some many samples, is because people often mess
> up when they work with dynamic SQL and ask for help.)
>
> I purposely did not include any sample code, because there is not really
> any reason to build the string in T-SQL, even if it's possible. It may
> be better to do this client-side, as client-side languages are better on
> string manipulation.
>
> What's important to understand is that a given query, always returns a
> fixed set a columns. This is why you have to use dynamic SQL.
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|