sql - 将表名作为参数传递

Question

我的任务是使用要从外部来源引入的信息来更新几个表。为此，我一直在网上寻找将表名作为参数传递的方法，所有答案都很复杂和/或抛出错误(如下所示:“Incorrect syntax near 'Table' error shows)

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

这个脚本在显式命名时工作得很好，那么如何参数化表名？

谢谢，

内特

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)

举个例子