Stored procedure scope: using sp from another database

Question

I hava a stored procedure named PROC_fetchTableInfo (simple select from sys.tables) in a database named

Cust

I would like use this procedure in a another database named

Dept

I tried to execute this sp in this database using command

EXECUTE Cust.dbo.PROC_fetchTableInfo

but as a result I get tables from the database Cust. How to get this procedure to work in database Dept?

Answer 1

A stored procedure is tighly bound to the objects in its code. If Cust.dbo.PROC_fetchTable references a table T, that is stricly the table T in schema dbo in database Cust. You can invoke the procedure from any other place, it will always refer to this table.

If you need to run the same procedure in another table on another database then the best solution, by far, is to have a new procedure: Dept.dbo.PROC_fetxTableInfo. This is better than the alternative of using Dynamic-SQL. While this seems counteruintuitive from a DRY and code reuse perspective, T-SQL is a data access language is not a programming language, leave your C/C# mind set at the door when you enter the database. Just have another procedure in the Dept database.

Answer 2

Your procedure, Cust.dbo.PROC_fetchTableInfo looks at data in the Cust database. Sounds like you need a copy of it in the Dept database.

Alternately (and quite unsatisfactory to me) you can add a parameter that controls which database to query by building the query in the sproc dynamically.

Perhaps you could create it in some "Common" database and call it like EXEC Common..PROC_fetchTableInfo @databaseName='Dept'