假设我有一个SQL语句,它在语法和语义上都是正确的,所以它可以执行。
在Management Studio (或任何其他查询工具)中,我如何测试SQL语句,如果我注意到它们破坏了某些东西,回滚(在单独的查询中?)
发布于 2009-02-03 10:27:44
最简单的做法是将代码包装在一个事务中,然后逐行执行每一批T-SQL代码。
例如,
Begin Transaction
-Do some T-SQL queries here.
Rollback transaction -- OR commit transaction
如果您想合并错误处理,可以使用TRY...CATCH块来实现。如果发生错误,您可以在catch块中回滚传输。
例如:
USE AdventureWorks;
GO
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
有关更多详细信息,请参阅以下链接。
http://msdn.microsoft.com/en-us/library/ms175976.aspx
希望这有帮助,但请让我知道,如果你需要更多的细节。
发布于 2016-06-30 23:50:11
我想添加一个点,如果你在测试模式下,你也可以(如果你写的东西很复杂,也应该)添加一个测试变量到回滚。然后你就可以一次执行整个代码了。通常,我还会添加代码来查看各种操作的前后结果,特别是当它是一个复杂的脚本时。
示例如下:
USE AdventureWorks;
GO
DECLARE @TEST INT = 1--1 is test mode, use zero when you are ready to execute
BEGIN TRANSACTION;
BEGIN TRY
IF @TEST= 1
BEGIN
SELECT *FROM Production.Product
WHERE ProductID = 980;
END
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
IF @TEST= 1
BEGIN
SELECT *FROM Production.Product
WHERE ProductID = 980;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0 AND @TEST = 0
COMMIT TRANSACTION;
GO
https://stackoverflow.com/questions/506602
复制相似问题