根据存储过程的输入动态选择不同的数据库
我正在尝试从 1 个特定数据库的硬编码选择更改数据库中的存储过程，以便能够根据传递到存储过程的 id 从任何数据库中进行选择。下面是存储过程为我们做的事情的存根：
ALTER PROCEDURE [dbo].[GetByAdId]
(
@AdId int,
@UserCompanyId int
)
AS
SET NOCOUNT ON
SELECT
[User].[UserId],
UserMappings.IsActive,
IsAccountOwner = ( SELECT Count(*) FROM DB1_SetUp.dbo.ad Adv WHERE Adv.AdID = UserMappings.AdID AND Adv.PrimaryAccountOwnerID = [User].[UserId] )
FROM
[User] INNER JOIN UserMappings ON
(
UserMappings.UserID = [User].UserID
AND UserMappings.AdID = @AdId
AND UserMappings.UserCompanyId = @UserCompanyId
)
基本上，“IsAccountOwner”变量每次都被硬编码为从 DB1_SetUp 中进行选择，但是我们有许多针对不同组的 SetUp 数据库，例如 DB2_SetUp、DB3_SetUp 等。 UserCompanyId 变量像组 Id 一样被传递到 sproc 函数中，并且可用于指向我希望它从中选择的特定 SetUp DB，但我不知道该怎么做。我基本上想要类似的东西：
SELECT * FROM (
CASE @UserCompanyId
WHEN 3 THEN 'DB3_SetUp'
WHEN 4 THEN 'DB4_SetUp'
)
有没有一种干净的方法来做到这一点，或者我是否必须在每个组数据库上设置这个存储过程并在每个数据库上调用特定的存储过程？
I'm trying to alter a stored procedure in our DB from a hard-coded select from 1 specific DB to be able to select from any of our DB's based on an id that's passed into the sproc. Here's the stub of what the sproc is doing for us:
ALTER PROCEDURE [dbo].[GetByAdId]
(
@AdId int,
@UserCompanyId int
)
AS
SET NOCOUNT ON
SELECT
[User].[UserId],
UserMappings.IsActive,
IsAccountOwner = ( SELECT Count(*) FROM DB1_SetUp.dbo.ad Adv WHERE Adv.AdID = UserMappings.AdID AND Adv.PrimaryAccountOwnerID = [User].[UserId] )
FROM
[User] INNER JOIN UserMappings ON
(
UserMappings.UserID = [User].UserID
AND UserMappings.AdID = @AdId
AND UserMappings.UserCompanyId = @UserCompanyId
)
Basically the "IsAccountOwner" variable is hardcoded to select from DB1_SetUp every time, but we have a number of SetUp db's for different groups, so like DB2_SetUp, DB3_SetUp, etc. The UserCompanyId variable being passed into the sproc functions like a group Id and can be used to point to the particular SetUp DB I want it to select from, but I'm not sure how to do this. I basically wanted something on the ilk of:
SELECT * FROM (
CASE @UserCompanyId
WHEN 3 THEN 'DB3_SetUp'
WHEN 4 THEN 'DB4_SetUp'
)
Is there a clean way to do this, or will I have to setup this sproc on each group DB and call the specific one over on each DB?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我过去通过动态构建我想要执行的 SQL（基于传入的参数）然后使用 sp_executesql 执行 SQL 来完成此操作，
请参阅：http://msdn.microsoft.com/en-us/library/ms188001.aspx
I've done this in the past by dynamically building the SQL I wanted to execute (based on parameters passed in) and then executing the SQL using sp_executesql
see: http://msdn.microsoft.com/en-us/library/ms188001.aspx