在 sql server 中使用动态模式名​​称时编写存储过程 [英] Writing stored procedures when using dynamic schema names in sql server

问题描述

我目前使用的应用程序的表有不同的模式名称，例如 Table1 可以有多个存在，比如 A.Table1 和 B.Table1.我所有的存储过程都存储在 dbo 下.我正在使用动态 SQL 编写以下存储过程.我目前使用的是 SQL Server 2008 R2，很快就会迁移到 SQL Server 2012.

The application, I have been currently working with has different schema names for its tables, for example Table1 can have multiple existence say A.Table1 and B.Table1. All my stored procedures are stored under dbo. I'm writing the below stored procedures using dynamic SQL. I'm currently using SQL Server 2008 R2 and soon it will be migrated to SQL Server 2012.

create procedure dbo.usp_GetDataFromTable1
@schemaname varchar(100),
@userid bigint
as
begin
    declare @sql nvarchar(4000)
    set @sql='select a.EmailID from '+@schemaname+'.Table1 a where a.ID=@user_id';
    exec sp_executesql @sql, N'@user_id bigint', @user_id=@userid
end

现在我的问题是，1. 这种方法会影响我的存储过程的性能吗?2、如果影响性能，那么这种场景怎么写程序?

Now my questions are, 1. Is this type of approach affects the performance of my stored procedure? 2. If performance is affected, then how to write procedures for this kind of scenario?

推荐答案

动态模式和相同的表结构很不寻常，但你仍然可以使用这样的东西获得你想要的:

Dynamic schema and same table structure is quite unusual, but you can still obtain what you want using something like this:

declare @sql nvarchar(4000)
declare @schemaName VARCHAR(20) = 'schema'
declare @tableName VARCHAR(20) = 'Table'
-- this will fail, as the whole string will be 'quoted' within [..]
-- declare @tableName VARCHAR(20) = 'Category; DELETE FROM TABLE x;'

set @sql='select * from ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)
PRINT @sql

-- @user_id is not used here, but it can if the query needs it
exec sp_executesql @sql, N'@user_id bigint', @user_id=0

因此，QUOTENAME 应该对 SQL 注入保持安全.

So, QUOTENAME should keep on the safe side regarding SQL injection.

1.性能 - 动态 SQL 无法从某些性能改进中受益(我认为与过程相关的统计信息或类似的东西)，因此存在性能风险.

1. Performance - dynamic SQL cannot benefit from some performance improvements (I think procedure associated statistics or something similar), so there is a performance risk.

但是，对于运行在少量数据(最多数千万)上的简单事物和变化不大的数据(插入和删除)，我认为您不会有明显的问题.

However, for simple things that run on rather small amount of data (tens of millions at most) and for data that is not heavily changes (inserts and deletes), I don't think you will have noticeable problems.

2.替代 -bukko 提出了一个解决方案.由于所有表具有相同的结构，它们可以合并.如果它变得巨大，良好的索引和分区应该能够减少查询执行时间.

2. Alternative -bukko has suggested a solution. Since all tables have the same structure, they can be merged. If it becomes huge, good indexing and partitioning should be able to reduce query execution times.

这篇关于在 sql server 中使用动态模式名​​称时编写存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！