我在Insert
语句中使用了Output
子句,这需要使用Table Variable
。我还希望Table name
是动态的,所以我使用dynamic SQL
,但它不允许使用Table Variable
。我得到了错误Must declare the scalar variable "@InsertedId"
。
CREATE PROCEDURE sp_InsertPerson @Name varchar(50), @Table varchar(20) AS
DECLARE @InsertedId TABLE (Id int)
DECLARE @SQL nvarchar(200) = 'INSERT INTO ' + @Table + ' (Name) OUTPUT INSERTED.Id INTO ' + @InsertedId + ' VALUES (' + @Name + ')'
IF (@Name is not null AND @Name != '')
EXEC(@SQL)
SELECT Id FROM @InsertedId
如何同时使用Output
子句和动态Table name
发布于 2018-12-17 14:24:56
试试这个;
CREATE PROCEDURE sp_InsertPerson @Name varchar(50), @Table varchar(20) AS
DECLARE @SQL nvarchar(200) = ''
SET @SQL = @SQL + 'DECLARE @InsertedId TABLE (Id int)';
SET @SQL = @SQL + 'INSERT INTO ' + @Table + ' (Name) OUTPUT INSERTED.Id INTO @InsertedId (Id) VALUES (''' + @Name + ''')'
SET @SQL = @SQL + 'SELECT Id FROM @InsertedId'
IF (@Name is not null AND @Name != '')
EXEC(@SQL)
https://stackoverflow.com/questions/53809833
复制相似问题