选择into是在尝试为每个表生成动态更新语句时选择最后一个记录。
选择into是在尝试为每个表生成动态更新语句时选择最后一个记录。
提问于 2022-11-15 00:33:13
我有一张桌子如下所示：
档案：
TableName ColumnName Datatype NewValue
Employee FirstName nvarchar Abc
Employee LastName nvarchar Pqr
Employee Age int 28
Address City nvarchar Chicago
Address StreetName nvarchar KKKKK我试图为每个表创建动态update语句，并希望在任何更新失败时为每个表执行update语句和rollback。
每个表的预期update语句查询：
Update Employee set FirstName = 'Abc', LastName = 'Pqr', Age = 21
where DepartmentId = 100;
Update Address set City = 'Chicago', StreetName = 'KKKKK'
where DepartmentId = 100;存储过程：
Alter PROC [dbo].[UpdateDataByDeptID]
@departmentId int
As
Begin
Declare db_cursor CURSOR for select TableName from Archival
group by TableName
Declare @tableName nvarchar(50),
@columnName nvarchar(50),
@datatype nvarchar(50),
@newValue nvarchar(50)
open db_cursor
Fetch Next from db_cursor into @tableName;
While @@FETCH_STATUS = 0
Begin
select @columnName = ColumnName,
@datatype = Datatype,
@newValue = NewValue
from Archival where TableName = @tableName;
print @tableName + ' ' + @columnName + ' ' + @newValue
Fetch Next from db_cursor into @tableName
End;
close db_cursor;
DEALLOCATE db_cursor;
Begin Transaction
Begin Try
--execute each of the update statement like below for ex:
Update Employee .....
Update Address .....
Commit transaction
End Try
Begin Catch
Rollback
End Catch
End
End -- end of SP但问题是这条线：
print @tableName + ' ' + @columnName + ' ' + @newValue上线给了我每张表的最后一张唱片。例如，对于Employee表：
Employee Age int 28因此，我无法为每个表生成单独的update语句。
在每个表的每个update语句之后，我的事务的最终预期输出如下：
Begin Transaction
Begin Try
--execute each of the update statement like below for ex:
Update Employee set FirstName = 'Abc', LastName = 'Pqr', Age = 21
where DepartmentId = 100;
Update Address set City = 'Chicago', StreetName = 'KKKKK'
where DepartmentId = 100;
Commit transaction
End Try
Begin Catch
Rollback
End Catch
End有人能帮我吗？
Stack Overflow用户
回答已采纳
发布于 2022-11-15 02:12:44
您可以通过使用动态SQL来处理类似的问题。
例如：
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tablename NVARCHAR(255);
DECLARE db_cursor CURSOR FOR
SELECT DISTINCT [TableName] FROM [Archival];
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql += N'UPDATE ' + QUOTENAME(@tablename) + ' SET ';
SELECT @sql += QUOTENAME([ColumnName]) + N' = ' + QUOTENAME([NewValue], '''') + N', '
FROM [Archival]
WHERE [TableName] = @tablename;
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1) + N'
WHERE DepartmentId = 100;
';
FETCH NEXT FROM db_cursor INTO @tablename;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
PRINT @sql;
-- EXEC sp_executesql @sql;我相信，这会给你带来一些预期的产出。
但是，如果您不能信任NewValue专栏中的内容，那么在执行类似的操作时，我会非常小心。(例如，如果有人可能在这里执行某种恶意SQL )。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74439424
复制相关文章
相似问题
腾讯云开发者
