Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69

    Unanswered: complex query help needed

    Hello all,

    I'm stuck with this one:

    Step 1
    I have a stored proc like this

    SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view1 on table1.id = view1.id
    WHERE (bunch of criteria)

    view one basically returns ToDo0 ... ToDo8

    Everything works fine.

    Step 2

    As I have different ToDo's depending on who is logged on, there are view2 ... view6 returning the ToDo's accordingly. So I have

    If @grp = 1
    SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view1 on table1.id = view1.id
    WHERE (bunch of criteria)
    else if @grp = 2
    SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view2 on table1.id = view2.id
    WHERE (same bunch of criteria)
    else ... (you get the point

    works fine, though a little slow.

    Step 3

    To keep things maintainable (I'm not the only one working on that) and somewhat modular, I'd like to have something like

    SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN just-take-the-right-view-please as Yep on table1.id = Yep.id
    WHERE (bunch of criteria)

    No Go ...

    I tried:

    1.
    Create #ttbl_ToDo (...)
    if @grp = 1
    Insert #ttbl_ToDo SELECT * from view1
    else ...

    and then joining on the #ttbl

    I got timeouts (view1 ... 6 are quite expensive).

    2.
    Built a stored proc that already returns ToDo1 .. 8 for the right group but then I can't access the resultset from the calling sp.

    3.
    Try to build dynamic SQL with EXEC (expected timeouts there, too) - the SQL string exceeds maximum length (as things are a little more complex in reality)

    I'm using MSSQL 7 (no option to migrate to 2000 an use functions yet )

    Some more explanation why I'm not happy with Step2 (which at least is working):
    1. the where clause is kind of complex an needs to be adopted from time to time. It's just a pain to do this 6 times.
    2. Other developers should be able to add ToDo-groups without changing the query itself. Changing the part with the temp table wouldn't be perfect but acceptable, but changing the whole thing is not what we want.

    Any hints are appreciated.

    TIA, Chris

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    To simplify you could use dynamic SQL, which as you stated cause timeouts, so I don't know if this will help.

    Code:
    DECLARE @view varchar(35)
    
    SELECT @view = CASE
                        WHEN @grp=1 THEN "view1"
                        WHEN @grp=2 THEN "view2"
                        WHEN @grp=3 THEN "view3"
                        WHEN @grp=4 THEN "view4"
                        WHEN @grp=5 THEN "view5"
                        ELSE "view6"
                    END
    
    EXEC ("SELECT .... FROM... " + @view + " WHERE....")
    If your views only differed by a WHERE clause like this "@grp" value then you could combine the views into one view and leave off the WHERE criteria until you use it.

    CREATE VIEW view1 AS
    SELECT ......
    WHERE grp = 1

    CREATE VIEW view2 AS
    SELECT ......
    WHERE grp = 2

    etc.

    Change to

    CREATE VIEW view AS
    SELECT ......

    Then
    SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view on table1.id = view.id
    WHERE (bunch of criteria)
    AND view.grp = @grp <-- Add here

    This would not require dynamic SQL.

    Also you can execute a stored procedure and have it's result go into a table.

    INSERT table EXEC myProc

    Note:
    You have to watch out with views they are not a performance saver. If the view is a 6 table JOIN then when you execute it, it is a 6 table JOIN.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •