如何用表名作参数 出错

[勉励前行]

CREATE PROCEDURE [dbo].[TransTB]
@tablename varchar(254)
AS
declare @sql nvarchar(1280)
BEGIN
set @sql =
'
UPDATE @tablename
SET ID_ManuParts = 40
where ID_ManuParts = 458

UPDATE @tablename
SET ID_ManuParts = 50
where ID_ManuParts = 1352

UPDATE @tablename
SET ID_ManuParts = 60
where ID_ManuParts = 1353

UPDATE @tablename
SET ID_ManuParts = D.更改
FROM @tablename LEFT JOIN
(SELECT C.ID ,B.Part,B.Change FROM (
SELECT Name, Part,Change FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
''Data Source="d:\kqshare\TransDayWorks.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[a$]) B
LEFT JOIN dbo.Manufacture C ON B.Name = C.UniCode) as D
ON @tablename.ID_Manufacture = D.ID AND @tablename.ID_ManuParts = D.Part
WHERE D.ID is not null and D.Change is not null
'
EXECUTE sp_executesql @sql
END


执行过程：EXEC TransTB MP_WorkRecord_ACF
报錯：
Msg 1087, Level 15, State 2, Line 2
必須宣告資料表参数數 "@tablename"。

问过程该如何写

[叶子]

CREATE PROCEDURE [dbo].[TransTB]
@tablename varchar(254)
AS
declare @sql nvarchar(1280)  
BEGIN
set @sql =
'
UPDATE '+@tablename+'
SET ID_ManuParts = 40   
where ID_ManuParts = 458  

UPDATE '+@tablename+'
SET ID_ManuParts = 50
where ID_ManuParts = 1352  

UPDATE '+@tablename+'
SET ID_ManuParts = 60
where ID_ManuParts = 1353  

UPDATE '+@tablename+'
SET ID_ManuParts = D.更改
FROM '+@tablename+' LEFT JOIN  
(SELECT C.ID ,B.Part,B.Change FROM (
SELECT Name, Part,Change FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
  ''Data Source="d:\kqshare\TransDayWorks.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'')...[a$]) B
LEFT JOIN dbo.Manufacture C ON B.Name = C.UniCode) as D  
ON @tablename.ID_Manufacture = D.ID AND @tablename.ID_ManuParts = D.Part
WHERE D.ID is not null and D.Change is not null
'
EXECUTE sp_executesql @sql
END

动态拼接，变量要在外面。

[--小F--]

表名做参数 需要动态拼接

[快溜]

动态拼接字符串，用exec执行，你这样写肯定是没有该表的。