Dynamic Datasource In Sql Server Stored Procudure

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

Solution 1:

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

DECLARE@theSqlvarchar(1000)
DECLARE@installIdintSET@installId=1SET@theSql='SELECT COUNT(*) FROM dbo.Installation'+CAST(@installIdas nvarchar) +'.Names'EXEC  (@theSql)

Solution 2:

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.

Solution 3:

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEPROCEDURE GetInstallationCount
-- Add the parameters for the stored procedure here@installIdint=0ASBEGINSET NOCOUNT ON;
  DECLARE@sql nvarchar(MAX)

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

Solution 4:

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.

Solution 5:

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

IFobject_id(N'SN', N'dbo.MySpecialTable') ISNOTNULLDROPSYNONYMdbo.MySpecialTableCREATESYNONYMdbo.MySpecialTableFOR[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.