Does anyone know if it is possible to write a Stored Procedured and execute it from different schemas (using its particular tables)?
I have two tables with the same name over different schemas:
* db1.User (id int, name varchar(50), surname varchar(50))
* db2.User (id int, name varchar(50), surname varchar(50), email varchar(100))
and the following procedure:
CREATE PROCEDURE sp_getusers
SELECT * FROM User
If I execute the sp_getusers procedure from a login with default_schema db1, I´d like the procedure to return the User table from that schema not the one from the procedure´s schema.
But if I logged in to SQL Server with default_schema db2, I´d like the procedure to return the second table.
I wonder if this is posible without recompiling each procedure on each schema.
Thanks in advance.