Query/View/Stored Proc structure - please help!? 
Author Message
 Query/View/Stored Proc structure - please help!?

Hi.

I'm really stuck with this problem, I just can't seem to get my head
around how I should approach this in SQL.

A program I've written has evolved into quite a large system, and I'm
trying to clean up some areas of it which are giving me problems.
It's a manpower system, and I'm specifically trying to write a unified
piece of code to report which user can see what records in the
database.  The logic is something like:

- If they are a regional manager, they can see all the branches in
their region
- If they are a branch manager, they can see all the branches they can
manage
- If they are an "open access" user, they can see ALL regions.
- etc
- etc

There are about 6 or 7 levels of heirarchy, exceptions and rules, so
it's a biggish (40-60 lines) query to work out someone's "domain" of
records that they have access to.

At present, we've got this logic written into about 5 reports, our
basic search page, and one or two other areas of the system.  My
object oriented programming background says to me I should be able to
write a stored proc called "GetMyRecords" which takes a parameter
"UserID".  I would consume it in each of my reports or searches by
saying.

SELECT blah blah
FROM "GetMyRecords UserID"
WHERE sFirstName like 'John%'

or whatever.  However, I just can't find an elegant way to "reuse" a
query like this.  Can I consume the results of a stored proc from
within another stored proc?  Can I have a parameterized view?  Should
I be using temp tables between SPs or something?

Please help - I'm feeling rather stuck.
Thanks in advance.
Cheers
Hilton



Tue, 07 Mar 2006 10:39:16 GMT
 Query/View/Stored Proc structure - please help!?

you cand use a parametrized inline table-valued function (see books online -
create function)

or a view with a column 'userID'


Quote:
> Hi.

> I'm really stuck with this problem, I just can't seem to get my head
> around how I should approach this in SQL.

> A program I've written has evolved into quite a large system, and I'm
> trying to clean up some areas of it which are giving me problems.
> It's a manpower system, and I'm specifically trying to write a unified
> piece of code to report which user can see what records in the
> database.  The logic is something like:

> - If they are a regional manager, they can see all the branches in
> their region
> - If they are a branch manager, they can see all the branches they can
> manage
> - If they are an "open access" user, they can see ALL regions.
> - etc
> - etc

> There are about 6 or 7 levels of heirarchy, exceptions and rules, so
> it's a biggish (40-60 lines) query to work out someone's "domain" of
> records that they have access to.

> At present, we've got this logic written into about 5 reports, our
> basic search page, and one or two other areas of the system.  My
> object oriented programming background says to me I should be able to
> write a stored proc called "GetMyRecords" which takes a parameter
> "UserID".  I would consume it in each of my reports or searches by
> saying.

> SELECT blah blah
> FROM "GetMyRecords UserID"
> WHERE sFirstName like 'John%'

> or whatever.  However, I just can't find an elegant way to "reuse" a
> query like this.  Can I consume the results of a stored proc from
> within another stored proc?  Can I have a parameterized view?  Should
> I be using temp tables between SPs or something?

> Please help - I'm feeling rather stuck.
> Thanks in advance.
> Cheers
> Hilton



Tue, 07 Mar 2006 11:23:02 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Please help to convert view on views to stored procedure

2. Please help to convert view on views to stored procedure

3. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

4. Cant edit stored proc in Access design view HELP

5. Help Please with Faulty ObjectPAL Query Structure...

6. !!!! PLEASE HELP ::stored proc call dies with ODBC

7. please help stored proc issue

8. Please Help, Nested stored proc is inconsitent

9. PLEASE HELP - stored proc

10. Stored Proc Date conversion Problem, Please Help

11. -----need help with stored proc---PLEASE

12. Please Help with Stored Proc


 
Powered by phpBB® Forum Software