如何在SQL Server中将模式作为参数传递给存储过程？

Question

我有一个存储过程，基于两个表来选择数据列表。第一个表是固定的：CO.Country。但第二个表可能是多个表中的一个。表名本身相同：Location。但是，这些表的模式不同：ABD.Location, CGA.Location, GBN.Location. 用户将从应用程序中选择模式，然后所选模式将作为参数传递给存储过程。

但在创建存储过程时解析时出现了错误。

有没有办法将模式名称作为参数传递？

- user2621831

---

展示你的存储过程。你尝试了什么？ - slavoo

Answer 1

使用动态Sql

尝试这样做

CREATE PROCEDURE proc_name
   @schema VARCHAR(25)
AS

DECLARE @Query VARCHAR(1000)
SET @query='SELECT * FROM ' +@schema +'.Location'
EXECUTE(@query)

- Nithesh Narayanan

---

这个很管用。我尝试使用标准的存储过程查询：select * from @schema.Location. 甚至无法执行。非常感谢。 - user2621831

如果我需要普通查询（不使用DynamicSql），那么在这种查询中传递模式变量是否可行？ - Misi

@Misi 我认为不行。你需要将查询构建为字符串，因此必须使用动态 SQL。 - Nithesh Narayanan

仅供提醒和警惕，值得指出的是，这种方法容易受到SQL注入攻击。例如，如果参数稍微大一些（VARCHAR(35)），那么使用 EXEC dbo.proc_name @schema = 'sys.stats;DROP PROC proc_name;--' 调用存储过程将会导致该过程自行删除。 - Richardissimo

看起来可以通过执行 SET @query='SELECT * FROM ' +QUOTENAME(@schema) +'.Location' 来避免 SQL 注入攻击的漏洞。 - Richardissimo