Dynamic Datasource in SQL Server Stored Procudure

Question

I have a SQL Server that houses Several Databases. I have a Main Database that holds several tables with entities and ID numbers. Then, each one of those entities has a correlating database (not a table, but database) with all of its information. For example, if the an entity in the MAIN database has an ID number of 1, there would be an SubDatabase1 Database on the same SQL Server.

Essentially, what I am trying to do is create a stored procedure in the MAIN Database, that collects data from the SUB Database, but the SUB database I collect from should be determined based on the ID number passed to the Proc.

I know this is totally incorrect, but I am wondering if someone can shine some light on this for me.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetInstallationCount
-- Add the parameters for the stored procedure here
@installId int=0
AS
BEGIN
  SET NOCOUNT ON;

  //Trying to make the DatabaseName dynamic here!!
  select count(*) from [email protected]

END
GO

Thanks - J

Answer 1

You have to use dynamic SQL to do that. Table names and database names cannot be resolved at runtime in any other way.

Here is a good introduction to this technique by Scott Mitchell.

Answer 2

Read up on how to create dynamic SQL, particularly sp_executesql. This should get you started:

DECLARE @theSql varchar(1000)
DECLARE @installId int
SET @installId = 1
SET @theSql = 'SELECT COUNT(*) FROM dbo.Installation' + CAST(@installId as nvarchar) + '.Names'
EXEC  (@theSql)

Answer 3

Definately could be done by building up the select string dynamically and executing but it would be nasty.

You could get very flashy and try create synonyms of the fly, use them in the queries and then drop them but I'm not sure it would be worth it.

Answer 4

As often, the answer to such a question is dynamic SQL:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetInstallationCount
-- Add the parameters for the stored procedure here
@installId int=0
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql nvarchar(MAX)

  SET @sql = 'select count(*) from dbo.Installation' + Cast(@installId as nvarchar) + '.Names'
  EXECUTE dbo.sp_executesql @sql

END
GO

Answer 5

Use synonyms. For example this sets synonym dbo.MySpecialTable to point to table dbo.SomeTable in database DB_3.

IF object_id(N'SN', N'dbo.MySpecialTable') IS NOT NULL 
     DROP SYNONYM dbo.MySpecialTable
CREATE SYNONYM dbo.MySpecialTable FOR [DB_3].[dbo].[SomeTable]

With this in place, write all your queries to use synonyms instead of real table names. Synonyms have DB scope, so manage "target switching" at one place, maybe in a stored procedure.