Let's say I have an SQL statement that's syntactically and semantically correct so it executes.
In Management Studio (or any other query tool) how can I test SQL statements, and if I notice that they broke something, rollback (in a separate query?)
The easisest thing to do is to wrap your code in a transaction, and then execute each batch of T-SQL code line by line.
Begin Transaction -Do some T-SQL queries here. Rollback transaction -- OR commit transaction
If you want to incorporate error handling you can do so by using a TRY...CATCH BLOCK. Should an error occur you can then rollback the tranasction within the catch block.
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
See the following link for more details.
Hope this helps but please let me know if you need more details.