Writing T-SQL Scripts: Batches and GO, Variables, IF/WHILE, TRY...CATCH, THROW and Transactional Scripts
A safe T-SQL script uses SET XACT_ABORT ON, validation first, a transaction inside TRY...CATCH, and ROLLBACK plus THROW in CATCH; with XACT_ABORT OFF a transaction stays committable after an error and half-done work can become permanent. With real output: SET vs SELECT assignment, GO as a tool command, Msg 208 slipping past CATCH, the ROLLBACK THROW trap and a rerunnable price update in chunks of 20.
Version note: The SQL queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10) with the series' Bookstore database, the sqlcmd examples with the mssql-tools18 sqlcmd from the SQL Server 2025 image, and the PostgreSQL comparisons on PostgreSQL 18.7.
Guess First: SET or SELECT?
We set both variables to 0 and read the price of a book that does not exist (999), one with SET and the other with SELECT. Do both show the same thing?
DECLARE @price_set DECIMAL(10,2) = 0, @price_select DECIMAL(10,2) = 0;
SET @price_set = (SELECT list_price FROM dbo.books WHERE book_id = 999);
SELECT @price_select = list_price FROM dbo.books WHERE book_id = 999;
SELECT @price_set AS with_set, @price_select AS with_select;with_set with_select
-------- -----------
NULL .00SET assigns the result of a subquery; if the subquery returns no row, the result is NULL. SELECT @x = ... assigns once per row; if there is no row, it makes no assignment at all and the variable keeps its old value. If you fill the same variable over and over in a loop, this difference turns into a real bug: you handle the "book not found" case with the value from the previous iteration. The behaviours also differ when there are several rows:
DECLARE @title NVARCHAR(200);
SELECT @title = title FROM dbo.books WHERE category_id = 4;
DECLARE @n INT = @@ROWCOUNT;
SELECT @title AS last_title, @n AS rows_read;
SET @title = (SELECT title FROM dbo.books WHERE category_id = 4);
SELECT 'after SET' AS step, @title AS title;last_title rows_read
------------------- ---------
The Handmaid's Tale 4
(1 rows affected)
Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
step title
--------- -------------------
after SET The Handmaid's Tale
(1 rows affected)There are four books in the Dystopian category; the SELECT assigned four times and the variable ended up with the last row read. Which row is "last" is not guaranteed without ORDER BY. SET, on the other hand, refused more than one value and raised Msg 512. The batch did not stop after the error: the next SELECT ran and @title kept its old value. The rule: if you expect a single value, write SET @x = (subquery); use SELECT when you fill several variables in one read, but make sure the key is unique. This post moves from single queries to scripts: batches and GO, variables, IF/WHILE, cursors, TRY...CATCH, THROW and transactional scripts, and sqlcmd variables. At the end we turn the price update script of Bookstore, the online bookstore used as the shared example across this site's series, into one that works in chunks, leaves no half-done work behind when something fails, and can be run again.
Batches and GO
A batch is the group of statements the client sends to the server in one go; the server compiles and runs it as one unit. GO is not T-SQL: it is a separator recognised by tools such as sqlcmd, SSMS and the SQL Server extension for Visual Studio Code. The tool splits the script at the GO lines and sends each part as a separate batch. The first consequence is the lifetime of variables:
DECLARE @batch_size INT = 20;
SELECT @batch_size AS in_first_batch;
GO
SELECT @batch_size AS in_second_batch;in_first_batch
--------------
20
(1 rows affected)
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@batch_size".A variable dies with its batch. If you want to carry a value across batches, you have to write it to a table (or a temp table) or use a sqlcmd variable. The second consequence is the reach of errors. A compile error stops the whole batch; most errors raised at run time stop only that statement:
SELECT COUNT(*) AS books FROM dbo.books;
SELEC 1;
GO
SELECT COUNT(*) AS books FROM dbo.books;
SELECT COUNT(*) FROM dbo.no_such_table;
SELECT 'still running' AS step;Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'SELEC'.
books
-----
77
(1 rows affected)
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.no_such_table'.In the first batch the typo is on line 2, yet the SELECT on line 1 did not run either: the batch could not be compiled. In the second batch the first query ran, the reference to a missing table raised Msg 208, and the rest of the batch (still running) did not run. The reason is deferred name resolution: SQL Server accepts a reference to a missing table at compile time, tries to recompile the statement when it is about to run, and gives up there. The Msg 512 above, by contrast, did not stop the batch. Instead of memorising which error stops what, we will build the script with XACT_ABORT and TRY...CATCH; but first, know that some statements want a batch of their own:
DROP VIEW IF EXISTS dbo.v_active_books;
CREATE VIEW dbo.v_active_books AS SELECT book_id, title FROM dbo.books WHERE is_active = 1;Msg 111, Level 15, State 9, Line 2
'CREATE VIEW' must be the first statement in a query batch.CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION and CREATE TRIGGER must be the first statement in the batch; in a tool you put a GO in between. In application code, however, there is no such thing as GO. If you hand a migration script as is to SqlCommand or Dapper, the server sees GO as an ordinary word:
EXEC sp_executesql N'DROP VIEW IF EXISTS dbo.v_active_books;
GO
CREATE VIEW dbo.v_active_books AS SELECT book_id, title FROM dbo.books WHERE is_active = 1;';
GO
EXEC sp_executesql N'SELECT COUNT(*) FROM dbo.books
GO';Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 9, Line 3
'CREATE VIEW' must be the first statement in a query batch.
--
77The first one failed; that is good, at least you notice. The second one is sneakier: GO was read as a table alias for dbo.books and the query ran silently. If you need to run scripts from an application, split the script at the GO lines yourself or use a tool that does it (DbUp, Flyway, SQL Database Projects); these tools are covered in the upcoming schema change post of the SQL Server Derinlemesine series. GO also has a form that takes a number; it runs the batch that many times, which is handy when generating test data.
IF, WHILE and Cursors
IF makes a single statement or a BEGIN...END block conditional. Its most common form in scripts is IF EXISTS: "if the work has already been done, do not do it again". Every book on sale should have a stock row; here is a piece that adds the missing ones and does no harm when it runs twice:
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM dbo.books AS b
WHERE b.is_active = 1
AND NOT EXISTS (SELECT 1 FROM dbo.stock_items AS s WHERE s.book_id = b.book_id))
BEGIN
INSERT INTO dbo.stock_items (book_id, quantity_on_hand)
SELECT b.book_id, 0
FROM dbo.books AS b
WHERE b.is_active = 1
AND NOT EXISTS (SELECT 1 FROM dbo.stock_items AS s WHERE s.book_id = b.book_id);
PRINT CONCAT(@@ROWCOUNT, ' stock rows created');
END
ELSE
PRINT 'every active book has a stock row';
GO 28 stock rows created
every active book has a stock rowGO 2 ran the same batch twice: the first round added 8 stock rows for books 70–77, the second round found nothing left to do. That is the essence of a rerunnable (idempotent) script: it checks the state first and does only what is missing. SET NOCOUNT ON turns off the "rows affected" messages and keeps the output of large scripts readable.
WHILE is the only loop in T-SQL; BREAK leaves the loop and CONTINUE moves to the next iteration. Seeing a loop in a series about set-based thinking may feel odd, but it has two legitimate uses: splitting a large change into chunks (the price script below) and running a separate statement for each object. The second is also the typical place for a cursor:
DECLARE @name SYSNAME, @sql NVARCHAR(400), @n INT;
DECLARE tables_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.tables WHERE name IN ('books', 'order_items', 'orders') ORDER BY name;
OPEN tables_cursor;
FETCH NEXT FROM tables_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'SELECT @n = COUNT(*) FROM dbo.' + QUOTENAME(@name) + N';';
EXEC sp_executesql @sql, N'@n INT OUTPUT', @n = @n OUTPUT;
PRINT CONCAT(@name, N': ', @n);
FETCH NEXT FROM tables_cursor INTO @name;
END;
CLOSE tables_cursor;
DEALLOCATE tables_cursor;books: 77
order_items: 680
orders: 379LOCAL FAST_FORWARD makes the cursor visible only in this batch, read-only and forward-only; unless you have a specific reason, make it your default. We put the table name into the dynamic SQL with QUOTENAME (post 19). But this example did not need a cursor; the same information is already in the catalog views (post 22):
SELECT t.name AS table_name, SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
WHERE t.name IN ('books', 'order_items', 'orders')
GROUP BY t.name
ORDER BY t.name;table_name row_count
----------- ---------
books 77
order_items 680
orders 379Before reaching for a cursor, ask: can this be done with a single UPDATE, INSERT ... SELECT or a query with window functions? Row-by-row processing is usually many times slower than its set-based counterpart. The places where a cursor is really needed are things that by nature happen one at a time, such as calling a stored procedure for each row or running a separate DDL or maintenance command for each object. The difference between table variables and temp tables for holding rows temporarily is covered in the SQL Server Derinlemesine series post "tempdb, Temp Tables and Table Variables: Which One When?".
What Does TRY...CATCH Catch, and What Not?
If an error with a severity above 10 that does not close the connection happens in the TRY block, control passes to the CATCH block. In post 22 we saw that ERROR_NUMBER, ERROR_MESSAGE and ERROR_LINE return values only inside CATCH. What matters is knowing what is not caught:
BEGIN TRY
SELECT COUNT(*) AS c FROM dbo.no_such_table;
END TRY
BEGIN CATCH
SELECT 'caught' AS step, ERROR_NUMBER() AS err_no;
END CATCH;
SELECT 'after' AS step;
GO
BEGIN TRY
EXEC sp_executesql N'SELECT COUNT(*) AS c FROM dbo.no_such_table;';
END TRY
BEGIN CATCH
SELECT 'caught' AS step, ERROR_NUMBER() AS err_no, ERROR_MESSAGE() AS msg;
END CATCH;Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.no_such_table'.
step err_no msg
------ ------ ----------------------------------------
caught 208 Invalid object name 'dbo.no_such_table'.The same Msg 208 never reached CATCH in the first batch, and after did not run either. The rule Microsoft Learn describes: compile errors and object name resolution errors are not caught when they occur at the same level as the TRY...CATCH; when the error occurs at a lower level (sp_executesql, a called stored procedure), it is caught. That is exactly what happened in the second batch. If you think "the CATCH will log it if the table is missing" in a deploy script, you are wrong; check for existence up front with OBJECT_ID (next post).
Messages with a severity of 10 or below are not errors either:
BEGIN TRY
RAISERROR('just a warning', 10, 1);
SELECT 'after severity 10' AS step;
END TRY
BEGIN CATCH
SELECT 'caught' AS step;
END CATCH;just a warning
step
-----------------
after severity 10This behaviour is useful for progress messages: RAISERROR with severity 0 sends an informational message without breaking the flow. In addition, a cancel sent by the client (an attention, for example a command timeout) and KILL do not reach CATCH either; in that case only XACT_ABORT guarantees that no transaction is left open.
XACT_ABORT, XACT_STATE and THROW
The state a transaction is left in when an error happens depends on the XACT_ABORT setting. Of two UPDATEs, the first succeeds and the second violates CK_books_list_price:
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRAN;
UPDATE dbo.books SET list_price = list_price + 10 WHERE book_id = 26;
UPDATE dbo.books SET list_price = -1 WHERE book_id = 28;
COMMIT;
END TRY
BEGIN CATCH
SELECT 'xact_abort off' AS mode, XACT_STATE() AS xact_state, @@TRANCOUNT AS trancount, ERROR_NUMBER() AS err_no;
IF XACT_STATE() <> 0 ROLLBACK;
END CATCH;
GO
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRAN;
UPDATE dbo.books SET list_price = list_price + 10 WHERE book_id = 26;
UPDATE dbo.books SET list_price = -1 WHERE book_id = 28;
COMMIT;
END TRY
BEGIN CATCH
SELECT 'xact_abort on' AS mode, XACT_STATE() AS xact_state, @@TRANCOUNT AS trancount, ERROR_NUMBER() AS err_no;
IF XACT_STATE() <> 0 ROLLBACK;
END CATCH;
SELECT list_price FROM dbo.books WHERE book_id = 26;mode xact_state trancount err_no
-------------- ---------- --------- ------
xact_abort off 1 1 547
(1 rows affected)
mode xact_state trancount err_no
------------- ---------- --------- ------
xact_abort on -1 1 547
(1 rows affected)
list_price
----------
390.50XACT_STATE() returns three values: 0 no open transaction, 1 an open transaction that can be committed, -1 an open transaction that cannot be committed ("doomed"; only reads and ROLLBACK are possible). With XACT_ABORT OFF the transaction was still committable after error 547: had someone written COMMIT in the CATCH, the 10 TRY increase on Dune would have become permanent and the change to Foundation would not. That is exactly half-done work. With XACT_ABORT ON the same error made the transaction uncommittable and the only correct move was to roll back; in the last row Dune's price is unchanged. When I tried to commit an uncommittable transaction I got "Msg 3930 ... The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction." That is why the pattern in CATCH is always the same: IF XACT_STATE() <> 0 ROLLBACK; (or IF @@TRANCOUNT > 0 ROLLBACK;), followed by THROW;.
THROW has one trap: the statement before it must end with a semicolon. When the semicolon is missing:
BEGIN TRAN;
BEGIN TRY
UPDATE dbo.books SET list_price = -1 WHERE book_id = 26;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK
THROW;
END CATCH;Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'THROW'.The parser tried to read ROLLBACK THROW as one statement and the batch was never compiled. Semicolons are optional in most places in T-SQL, but they are required before THROW, before a CTE and at the end of a MERGE; make ending every statement with one a habit. The effect of XACT_ABORT on the transaction log and savepoints are covered in the SQL Server Derinlemesine series post on transactions and the transaction log; the difference between THROW and RAISERROR and the stored procedure skeleton are in the same series' post "Stored Procedures: Parameters, Error Handling, Ownership Chaining and Safe Dynamic SQL". In short: in new code, pass errors on with THROW; keep RAISERROR for progress messages.
Bookstore: Splitting a Price Update into Chunks
The purchasing team loads the supplier's new price list into a staging table; the script applies these prices to books. Let's fill the list with an 8% increase and add one bad row on purpose:
DROP TABLE IF EXISTS dbo.price_updates;
CREATE TABLE dbo.price_updates (
book_id INT NOT NULL CONSTRAINT PK_price_updates PRIMARY KEY,
new_price DECIMAL(10,2) NOT NULL
);
INSERT INTO dbo.price_updates (book_id, new_price)
SELECT book_id, ROUND(list_price * 1.08, 0) FROM dbo.books;
UPDATE dbo.price_updates SET new_price = -1 WHERE book_id = 50;
SELECT COUNT(*) AS staged, SUM(CASE WHEN new_price < 0 THEN 1 ELSE 0 END) AS invalid FROM dbo.price_updates;staged invalid
------ -------
77 1On a catalog with millions of rows, doing this update in a single UPDATE bloats the transaction log and holds locks for a long time (the DELETE TOP (5000) advice in post 15). We split the script into id ranges; each chunk is committed in its own transaction, progress is reported immediately, and if something fails that chunk is rolled back and the script ends by saying which range it stopped at. On a real catalog the chunk size would be 5,000–10,000; with 77 books we use 20 so that we can see several chunks:
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @batch_size INT = 20, @batch_no INT = 0, @from_id INT, @to_id INT = 0, @rows INT, @msg NVARCHAR(2048);
WHILE 1 = 1
BEGIN
SET @from_id = @to_id + 1;
SELECT @to_id = MAX(book_id)
FROM (SELECT TOP (@batch_size) book_id FROM dbo.price_updates WHERE book_id >= @from_id ORDER BY book_id) AS next_batch;
IF @to_id IS NULL BREAK;
SET @batch_no += 1;
BEGIN TRY
BEGIN TRAN;
UPDATE b
SET list_price = p.new_price
FROM dbo.books AS b
JOIN dbo.price_updates AS p ON p.book_id = b.book_id
WHERE b.book_id BETWEEN @from_id AND @to_id
AND b.list_price <> p.new_price;
SET @rows = @@ROWCOUNT;
COMMIT;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK;
SET @msg = CONCAT(N'Batch ', @batch_no, N' (book_id ', @from_id, N'-', @to_id, N') failed: ', ERROR_MESSAGE());
THROW 50010, @msg, 1;
END CATCH;
RAISERROR(N'Batch %d: book_id %d-%d, %d rows updated', 0, 1, @batch_no, @from_id, @to_id, @rows) WITH NOWAIT;
END;Batch 1: book_id 1-20, 20 rows updated
Batch 2: book_id 21-40, 20 rows updated
Msg 50010, Level 16, State 1, Line 25
Batch 3 (book_id 41-60) failed: The UPDATE statement conflicted with the CHECK constraint "CK_books_list_price". The conflict occurred in database "sqlseries_ext_24", table "dbo.books", column 'list_price'.SELECT COUNT(*) AS updated FROM dbo.books AS b JOIN dbo.price_updates AS p ON p.book_id = b.book_id WHERE b.list_price = p.new_price;updated
-------
40Every line of the script has a reason:
- Chunks are chosen not with
OFFSETbut with a range that continues from the last processed id (keyset); each chunk starts where the previous one left off in the index and does not read the earlier ones again. SELECT @to_id = MAX(...)returns a value even when it finds no row: an aggregate always returns one row, and the result isNULL. That is why the "no row, no assignment" trap from the opening does not apply here and the loop ends withBREAK.b.list_price <> p.new_pricemakes the script rerunnable: it does not touch rows that are already updated.@@ROWCOUNTis copied into a variable right after theUPDATE(post 22).CATCHrolls back first, then throws the error again with a new message that includes the context. The original error number (547) is lost, but its text is inside the message; if the number itself must go through, write the context withPRINTand use a parameterlessTHROW;.- Progress is written with
RAISERROR ... WITH NOWAIT.PRINTmessages pile up in a buffer and may reach the client late;WITH NOWAITsends the message right away, so you can follow where a long script is in the Messages tab of SSMS as it runs.
The result: the 40 rows of the first two chunks were committed, and the third chunk was rolled back as a whole. If we wanted all or nothing, we would put the whole loop in a single transaction; the price of chunking is that after a failure the database is left partly updated. The script being rerunnable is what makes that manageable. Even better is catching the bad data before any chunk starts:
IF EXISTS (SELECT 1 FROM dbo.price_updates WHERE new_price <= 0)
BEGIN
SELECT book_id, new_price FROM dbo.price_updates WHERE new_price <= 0;
THROW 50011, N'price_updates contains non-positive prices; nothing was applied.', 1;
END;
PRINT N'staging data is valid';book_id new_price
------- ---------
50 -1.00
(1 rows affected)
Msg 50011, Level 16, State 1, Line 4
price_updates contains non-positive prices; nothing was applied.sqlcmd Variables, :r and :on error exit
The last step in making the script reusable is putting it into a file and parameterising it. sqlcmd (and SQLCMD Mode in SSMS) recognises its own commands that start with a colon: :setvar defines a variable that is used as $(Name), :r includes another file at that point, and :on error exit stops the script at the first error. I saved the loop above as price-update.sql with one difference: @batch_size INT = $(BatchSize) instead of @batch_size INT = 20. The deploy file that fixes the bad price and runs it again:
:on error exit
:setvar BatchSize 20
UPDATE dbo.price_updates SET new_price = 345.00 WHERE book_id = 50;
GO
:r price-update.sql
GO
SELECT COUNT(*) AS updated FROM dbo.books AS b JOIN dbo.price_updates AS p ON p.book_id = b.book_id WHERE b.list_price = p.new_price;(1 rows affected)
Batch 1: book_id 1-20, 0 rows updated
Batch 2: book_id 21-40, 0 rows updated
Batch 3: book_id 41-60, 20 rows updated
Batch 4: book_id 61-77, 17 rows updated
updated
-------
77The command is sqlcmd -S ... -d BookstoreDb -i deploy-prices.sql. The first two chunks updated 0 rows because their work was done in the previous run; in effect the script carried on where it had stopped. The variables are sqlcmd's own text substitution: $(BatchSize) is replaced with 20 before anything goes to the server. That is why, unlike T-SQL variables, they cross batch boundaries; but putting a value that comes from a user into a script this way opens the door to SQL injection, so use it only for values given by the person running the deploy. You can also pass it on the command line with -v BatchSize=5000.
Without :on error exit, sqlcmd carries on with the batches after the one that failed: in the first attempt above, the SELECT after Msg 50010 ran and returned 40. When I sent a bad price on purpose with the same deploy file, sqlcmd stopped after the error in the third chunk, the final SELECT did not run, and the process exited with code 1. If you run the deploy in a CI/CD pipeline, that exit code is what stops the pipeline; forget :on error exit or the -b option and a failed migration looks "successful".
Errors and Transactions in PostgreSQL
I ran the same two UPDATEs inside a transaction in my own PostgreSQL 18 container. Once the second UPDATE violated the check constraint, every command in the rest of the transaction failed with "current transaction is aborted, commands ignored until end of transaction block". After an error a PostgreSQL transaction behaves much like XACT_ABORT ON, and there is no setting to turn that off; the only way out is ROLLBACK. The counterpart of TRY...CATCH is the EXCEPTION WHEN check_violation THEN part of a PL/pgSQL block; when it runs, the changes made by the block are rolled back automatically (Dune's price stayed at 390.50), because the block opens an implicit savepoint. RAISE NOTICE is the progress message and RAISE EXCEPTION the counterpart of THROW. In psql the counterpart of :on error exit is \set ON_ERROR_STOP on; after the error psql stopped with exit code 3.
In Production
Give every data-changing script that is run by hand or from a pipeline the same skeleton: SET NOCOUNT ON, SET XACT_ABORT ON, validation first (IF EXISTS ... THROW), then the work; inside the work BEGIN TRY/BEGIN TRAN, and in CATCH IF XACT_STATE() <> 0 ROLLBACK; and THROW. At the end of the script @@TRANCOUNT must be 0. Split large changes into chunks and write each chunk to be rerunnable; keep the chunk size in a variable so you can tune it in production by watching log growth and blocking.
Report progress with RAISERROR ... WITH NOWAIT, with the range and row count in the messages; when a script stops halfway at midnight, those lines answer "where did it stop". Run sqlcmd in pipelines with -b or :on error exit. Do not send scripts containing GO from the application; use a migration tool or split the script yourself.
Common Pitfalls
SELECT @x = ...keeping the old value when no row is returned, and taking a last value in undefined order when several rows are returned.- Leaving the transaction open in
CATCH, or committing a half-done transaction that stayed committable withXACT_ABORT OFF. - Not ending the statement before
THROWwith a semicolon (Msg 102). - Assuming that a reference to a missing table will be caught by a
CATCHat the same level (Msg 208 is not caught). - Assuming a variable lives on after
GO(Msg 137). - Sending a script with
GOfrom application code; you either get a syntax error orGOsilently becomes an alias. - Doing row by row with a cursor what could be written set-based.
- Running sqlcmd in a pipeline without
:on error exit/-band counting a failed script as successful.
A Small Experiment
★☆☆ Change the price script to report progress with PRINT and add WAITFOR DELAY '00:00:02' after each chunk. In the Messages tab of SSMS, compare when PRINT and RAISERROR ... WITH NOWAIT messages appear.
★★☆ Add a log table to the script: for each chunk the first and last id, the number of updated rows and the time. When something fails, also write the error message to the log in CATCH. Careful: do you need to write the log row before or after the ROLLBACK?
★★★ Add a sqlcmd variable that switches the script to all-or-nothing mode (:setvar AllOrNothing 1): when it is 1, all chunks should run in one transaction and no price should change on failure. What happens to the rows you write to the log table in this mode?
There is one more kind of script: deploy scripts that change the schema. In the next and final post of the series we look at IDENTITY and SEQUENCE, computed columns, CREATE OR ALTER, DROP ... IF EXISTS and existence checks, and write DDL scripts that do not fail when they run twice.