Pass Schema into a stored procedure?

[bmahler]

OK, I have an application that can handle multiple site locations.
These locations each need their own independent data, so our solution was to create a new schema for each site. I have created a group of stored procedures in the dbo schema. When a new site is created, I create a new schema and a new user with that schema as their default. I then give that user Execute permissions on the stored precedures. This all works great up to one point.

I am able to create all the tables and their keys just fine, however I have a couple of tables that require some preinstalled data. I tried to have 2 procedures that contain insert statements, but for some reason, they are not being called against the default schema and are returning an error

Is there a way to pass a schema name into a stored procedure and then insert it before the table name in the procedure? Or is there some other way to do this that would be easier? I have tried this a few ways and keep getting errors.

Thanks for any help

[szlamany]

Can you show a bit of what those procedures look like? We've never used schema level distinction here - maybe if you show me what you have I can offer a suggestion...

[bmahler]

I was actually able to work around this, what I am doing is storing the procedures in the dbo schema and with each new schema we create, we make a login with that schema as it's default. I then se the Procedures to build the database, which worked fine and put the tables in that users default schema, however I was trying to do insert statements in a procedure, but it kept trying to insert into the dbo schema. I was able to work around this, by creating the common tables in the dbo schema and then just copying them to the new schema each time. It is working fine and is pretty quick as well. Thanks.