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, ChrisTo simplify you could use dynamic SQL, which as you stated cause timeouts, so I don't know if this will help.

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.