对数百个相同的数据库使用一组存储过程
我继承了一个项目，其中每个“客户”都有自己的数据库。有数百个数据库。当前未使用存储过程。
此处使用数据的最佳实践是什么？我是否将存储过程保留在“主”数据库中并使用动态 SQL 来处理数据？看来应该有更好的方法。我不想运行一个作业来将存储过程推送到数百个数据库以保持所有存储过程同步。
这个动态 SQL 有效，但我想要更好的方法。
CREATE PROCEDURE [Users_SELECT]
@DataBase nvarchar(20),
@UserID uniqueidentifier
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = ''
SET @sql += 'SELECT * FROM ' + @DataBase + '.dbo.Users u '
SET @sql += 'WHERE u.UserID=@UserID '
EXEC sp_executesql @sql, N'@UserID uniqueidentifier', @UserID
END
我尝试了 EXEC sp_executesql 'USE ' + @DataBase + '; GO' 然后运行 SELECT 但我无法让它工作。
I inherited a project where each 'customer' has its own database. There are hundreds of databases. Stored procedures are currently not being used.
What are the best practices for consuming data here? Do I keep my stored procedures in the "master" database and use dynamic SQL to muck with data? It seems like there should be a much better way. I don't want to have a job running to push stored procedures around hundreds of DBs to keep all the stored procedures in sync.
This dynamic SQL is working, but I want a better way.
CREATE PROCEDURE [Users_SELECT]
@DataBase nvarchar(20),
@UserID uniqueidentifier
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = ''
SET @sql += 'SELECT * FROM ' + @DataBase + '.dbo.Users u '
SET @sql += 'WHERE u.UserID=@UserID '
EXEC sp_executesql @sql, N'@UserID uniqueidentifier', @UserID
END
I tried EXEC sp_executesql 'USE ' + @DataBase + '; GO' then running a SELECT but I couldn't get that working.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不明白将存储过程部署到每个数据库有什么问题。
您还必须部署对架构的更改，因此您希望拥有适当的基础设施来自动执行此操作。如果没有，那么是时候构建该基础设施了。
I don't see whats wrong with deploying the stored procedures to each and every database.
You have to deploy changes to the schema as well, so you hopefully have infrastructure in place to do it automatically. If not it is time build that infrastructure.
我想通了。这可能会让 SQL 专家非常生气，但我对这个解决方案很满意。
我在“master”中创建了我的存储过程。我知道这通常是禁忌。然后我将
每个存储过程标记为系统对象。这样它们就可以在不同的上下文下被调用。然后我可以这样做：
它将在 DataBase200 数据库的上下文中运行，而无需实际将存储过程部署到该数据库。到目前为止效果很好。请记住，您必须在存储过程名称前添加
sp_前缀，以便将它们识别为系统对象。我希望这对某人有帮助。
I figured it out. This will probably make SQL experts extremely mad, but I'm happy with the solution.
I created my stored procedures in 'master'. I know this is usually a no-no. I then used
to mark each sproc as a system object. This way they can be called while under a different context. I can then do this:
and it will run in the context of the DataBase200 db, without having to actually deploy the sproc to that database. It is working great so far. Remember that you have to prefix your stored procedures names with
sp_in order for them to be recognized as system objects.I hope this helps someone.