Executing Stored Proc with same names in diferents Databases

[Edimator]

Hi, i have a server containing a database for each month of the year, each db have 5 tables and a 10 stored procedures. All tables and all sp have the same name in every database. eg.
---TABLES---
Jan01.dbo.RevenueDetail_tb
Feb01.dbo.RevenueDetail_tb
Mar01.dbo.RevenueDetail_tb

---STORED PROCEDURES---
Jan01.dbo.Get_Summary_sp(param1,param2)
Feb01.dbo.Get_Summary_sp(param1,param2)
Mar01.dbo.Get_Summary_sp(param1,param2)

I need some code or workaround to execute only one SP and decide which database is the right one.

My first try was making a database with no tables just the stored procedures, and joining the tables of all db in the stored procedure, but the performance is very "very" slow.

Each SP is executed from ASP pages.

I really apreciate any kind of help.

 

[MAK9974]

create those procs in master with sp_ as prefix and just call them

eg:
use master

create procedure sp_x as
select * from sysobjects

exec sp_x

use DB1

exec sp_x
use northwind


exec sp_x

 

[Edimator]

Thanks, i try this, but executing stored procedures from ASP pages i can't run the statement USE.

I think that passing the parameter [month] and the stored procedure decide where is the right one database

i try with the next sp but give an error

create proc Get_Summary_sp @Month varchar(20)
as
begin
declare @SQL varchar(255)
set @SQL = 'SELECT * FROM ' + @Month + '.dbo.RevenueDetail_tb'

select * from openquery(infonet5,@SQL)
end

I really apreciate any kind of help.

 

[tlbroadbent]

You cannot use a parameter or dynamic SQL in an openquery statement.

Mak9974's recommendation to create a system stored procedure is good. When you execute a system stored procedure in the context of a database, it runs as though it was created in that database. You don't need to execute a use statement. You simply need to fully qualify the SP name.

Exec Jan01.dbo.sp_Get_Summary(param1,param2)

This will execute the SP in the Jan01 DB even though it doesn't exist in that database. So if you create the SP in master and mark it as a system object, it can be executed in the other databases.

Create sp_Get_Summary As
SELECT * FROM dbo.RevenueDetail_tb
Go

--mark the SP as a system object
exec sp_MS_marksystemobject 'sp_Get_Summary' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.