How To Use Dbo Procedure To Execute For Different Schema?

I am using SQL Server 2008 R2, I have a schema [dbo], and in that schema, I have created a stored procedure dbo.GetAccount: SELECT * FROM tblAccount Then, I have created a schema

Solution 1:

If I'm correct,

SELECT * FROM tblAccount actualy means SELECT * FROM[dbo].[tblAccount]

So your procedure will always execute the tblAccount from the dbo schema.

If you want ( let's say ) have a "general" procedure that can be execute from more schemas I say you try with dinamic sql.

CREATEPROCEDURE [dbo].[GetAccout] 
        @schemaasdeclare@sqlvarchar(200)
    set@sql='SELECT * FROM ['+@schema+'].[tblAccount]'exec(@sql)

then you run exec [dbo].[GetAccout] ABC

This should select all info from YourSchema.tblAccount.

I can't test it right now, but I should work. Also remember the table must be in that schema!

But if you can't change all the the procedure I can't think of a solution.