将表名作为参数传递

Question

我负责维护多个表格的信息，并从外部来源更新这些表格。为此，我一直在网上寻找传递表格名称作为参数的方法，但所有答案都很复杂和/或会出现错误（比如下面的"表格附近语法不正确"错误）。

CREATE PROCEDURE sp_Insert_Delta 
-- Add the parameters for the stored procedure here
@tableName Table READONLY

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Delete rows in MIRROR database where ID exists in the DELTA database
Delete from [S1].[MIRROR].[dbo].@tableName
Where [ID] in (Select [ID] from [S2].[DELTAS].[dbo].@tableName)

-- Insert all deltas
Insert Into [S1].[MIRROR].[dbo].@tableName
Select * from [S2].[DELTAS].[dbo].@tableName

END
GO

这个脚本在显式命名时可以正常工作，那么我该如何将表名参数化呢？谢谢，Nate。

- n8.

---

顺便提一下：如果这是SQL Server，您不应该为存储过程使用sp_前缀。微软已经保留了该前缀供自己使用（请参阅命名存储过程），而且您将来可能会遇到名称冲突的风险。这也会影响您的存储过程性能。最好只是避免使用sp_，使用其他前缀或根本不使用前缀！ - marc_s

这个回答解决了你的问题吗？如何将表名传递给存储过程？ - Michael Freidgeim

Answer 1

使用动态SQL

DECLARE @sql as varchar(4000)
SET @sql = 'Delete from [S1].[MIRROR].[dbo].' + @tableName
+ ' Where [ID] in (Select [ID] from [S2].[DELTAS].[dbo].' + @tableName + ')'
EXEC(@sql)

作为一个例子。

- RustyH

---

动态SQL是一个好的解决方案--这点毋庸置疑。然而，我建议通过sp_executesql将其参数化。这样做有几个优点，其中最大的优点是防止SQL注入攻击。 - Dave Mason

非常好的观点，我自己对动态SQL（以及SQL）还很陌生，但最近在工作中一直在疯狂使用它。感谢提供链接。 - RustyH

我本来想编辑我的答案，加入Sp_executesql，但是我在想你会如何添加表名变量，因为它只是表名的最后一个，仍然需要添加到"[S2].[DELTAS].[dbo]."中。 - RustyH

Answer 2

必须先说明，由于SQL注入漏洞的存在，您不应该这样做。

换句话说，您想要验证输入，我会在下面给出一个示例：

CREATE PROCEDURE sp_Insert_Delta 
-- Add the parameters for the stored procedure here
@tableName varchar(max) READONLY

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF EXISTS(SELECT 1 FROM sys.tables WHERE [name] = @tableName)
BEGIN 

  -- Delete rows in MIRROR database where ID exists in the DELTA database
  exec("Delete from [S1].[MIRROR].[dbo]."+@tableName"+
  " Where [ID] in (Select [ID] from [S2].[DELTAS].[dbo]."+@tableName);

  -- Insert all deltas
  exec("Insert Into [S1].[MIRROR].[dbo]."+@tableName)
  exec("Select * from [S2].[DELTAS].[dbo]."+@tableName)
END
-- ELSE handle error.

END
GO

注意，我现在没有访问SQL服务器的权限，所以可能会有错别字。

- Hogan

Answer 3

短答案是，您无法将表名参数化。

长一点的回答是，您可以通过动态SQL来实现所需功能。看起来您正在使用SQL Server。有关详细信息，请参见问题Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)。

然而，需要将任意表名放入查询中的需求实际上是一种代码味道，这表明您的数据库设计和可能与您的E-R模型存在架构问题。

- Nicholas Carey