dbo owned stored procedure hitting another user owned table

I am trying to come up with a solution that does not involve having a version of every stored procedure for every user I have...

Here is the problem...

I am going to have multiple users that need to have their own "product table". The structures are going to be the same for all. We currently only have one user and it is a DBO... all stored procedures are dbo.[sp name]... is there any way to get it so that the product table in the SP will be the user owned product table and not the dbo table?

I have tried just taking out the dbo prefix with no luck... the user's default schema will match the table they own so when they do a straight select they get the right information but it is just the SPs that I can't seem to get to work...

The only thing that I have come up with is making the SPs dynamic with having the username as parameter.

Is there anything else I can try?

and SQL 2005 SP2 on Win 2003 SP2

No. Dynamic SQL is the only way to get the schema resolution to work like you want. Optionally, you can consider querying both the tables and adding a filter on the username value like below. The filter on the user name will get evaluated at compile/run-time thereby eliminating all the queries except one. This is of course cumbersome if you have many objects.

Code Snippet

select ...

from user1.table as t1

where CURRENT_USER = 'user1'

union all

select ...

from user2.table as t1

where CURRENT_USER = 'user2'

union all

select ...

from user3.table as t1

where CURRENT_USER = 'user3'

Why can't you create wrapper SPs for each table and call them from the dbo SP? Each wrapper SP will query the table under that schema.

|||

I figured it would not work that easily...

What do you mean by "wrapper SP"?