sql-server - 无效的对象名称 - SQL Server 2005

Question

当执行以下存储过程时，我得到无效的对象名称 dbo.Approved。对象 dbo.Approved 确实存在，所以大概这与我将表名作为参数传递的方式有关？

我还应该补充一点，我通过 .NET 或从 SMSS 中执行过程时遇到了错误。

 @tableName as nvarchar(100)
AS
 BEGIN

EXEC('

UPDATE T1
SET T1.NPTid = dbo.Locations.NPT_ID
FROM '  + '[' + @tableName +  '] As T1
INNER JOIN dbo.Locations  ON T1.Where_Committed = dbo.Locations.Location_Name
')

END

在收到 Joe 和 JNK 的帮助后进行编辑，存储过程现在是这个，但我收到错误

Msg 102, Level 15, State 1, Procedure sp_Updater, Line 14  

Incorrect syntax near 'QUOTENAME'.

新存储过程

@tableName as nvarchar(100),
@schemaName as nvarchar(20)
AS
BEGIN  



EXEC('
--Update NPT
UPDATE T1
SET T1.NPTid = dbo.Locations.NPT_ID
FROM '  + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + ' As T1
INNER JOIN dbo.Locations  ON T1.Where_Committed = dbo.Locations.Location_Name
')

END

Answer 1

在您的字符串中使用方括号，您的表引用将变成无效的 [dbo.Approved]。引用应改为 [dbo].[Approved]。

您可能需要考虑将架构名称和表名称作为两个单独的参数传递。

最好使用 QUOTENAME函数而不是对方括号进行硬编码。

declare @sql nvarchar(1000)

set @sql = N'UPDATE T1
SET T1.NPTid = dbo.Locations.NPT_ID
FROM '  + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' As T1
INNER JOIN dbo.Locations  ON T1.Where_Committed = dbo.Locations.Location_Name
'

EXEC (@sql)