SQL Server中的动态数据源存储Procudure [英] Dynamic Datasource in SQL Server Stored Procudure

问题描述

我有一个包含几个数据库的SQL Server。我有一个主数据库，其中包含具有实体和ID号的多个表。然后，这些实体中的每一个具有与其所有信息相关联的数据库（而不是表，但数据库）。例如，如果MAIN数据库中的一个实体的ID号为1，则在同一SQL Server上将有一个SubDatabase1数据库。

本质上，我是尝试做的是在MAIN数据库中创建存储过程，从SUB数据库收集数据，但是我收集的SUB数据库应根据传递给Proc的ID号确定。

我知道这是完全不正确的，但我想知道有人可以为我发光吗？

  SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 CREATE PROCEDURE GetInstallationCount 
  - 在此处添加存储过程的参数
 @installId int = 0 
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
 //尝试使DatabaseName动态在这里！ 
从[e-mail] 
 
中选择count（*）END 
 GO 
  

感谢 - J

解决方案

了解如何创建动态SQL ，特别是 sp_executesql 。这应该让你开始：

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

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 [e-mail]

END
GO

Thanks - J

解决方案

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)

这篇关于SQL Server中的动态数据源存储Procudure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！