"Прелесть" try catch в MS SQL описана, например, здесь: http://www.sommarskog.se/error_handling/Part1.html
(Мне искренне любопытно, как много программистов это читали/знают и обрабатывают ошибки правильно.)

сорри, это что-то новое в MSSQL подвезли?
я про
with inserted as (
insert ...
)

чтиво очень большое с множеством кейсов, может конкретно сказать, что я не так написал?
да, я знаю что надо откатывать транзакцию с проверкой.
но я не понимаю, если первая команда в try begin transaction, как в catch может попасть @@trancount=0 при условии что в try блоке не было отката?
и текст процедуры корректный.
Т.е. не этот кейс с "Invalid object name 'NoSuchTable'"

да вроде нет, я пока еще старовер.
А что вас смутило в конструкции? cte вроде как давно завезли

А вот SQL Server "понимает", и когда это случится, ошибка будет обработана неправильно.
Я к тому, что Ваше непонимание реальности не меняет, и каждая строка в примере:

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

там не просто так. ;)

cte над insert смутило
with inserted as (
insert ...
)

То, что MS SQL её никогда не поддерживал (и не поддерживает), может быть?
Вы чат не перепутали? ;)

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

я просто хочу разобраться, я с вами не спорю.
Everything else in the procedure should come after BEGIN TRY: variable declarations, creation of temp tables, table variables, everything. Even if you have other SET commands in the procedure (there is rarely a reason for this, though), they should come after BEGIN TRY.
вот это я практически никогда не делал...

а с примером так и не понял, в статье будет ошибка или на первом или на втором insert'е.
транзакция открыта в обоих случаях, когда будет ситуация с @@trancount=0 так и не увидел.
я понимаю, что в примере надо читать каждую строку, но видимо за деревьями леса не вижу.

Это же только первая часть статьи — для тех, кто хочет примеров / разобраться, там есть продолжение.

спасибо, посмотрю обязательно.

хм... вы правы... а слона то я и не заметил... :)

тогда ход конем
create table tbl_header(id int);
create table tbl_detail(header int,id int)

insert into tbl_detail(header,id)
select id,5 from
(
insert into tbl_header(id)
 OUTPUT INSERTED.id
 values(2)
 ) as t;
 
select header,id from tbl_detail;

так работает.

так а кто поддерживает?

PostgreSQL, например.

Ребят подскажите пожалуйста куда копать:
Есть один запрос, который в одинаковых условиях выполняется либо довольно быстро, либо зависает в cxconsumer, не могу понять, почему это по разному происходит, от чего это зав-т неужто план запроса раз в какое то время рисуется кривым?

а ты знаешь, что свое сообщение можно редактировать?)

Отредачил) простите)

Я в таких случаях параллелизм отключал или уменьшал через maxdop. В 2 из 3 случаях помогло)

Но при это те быстрые случае станут медленее, верно?

покажите запрос и планы, там видно будет

Не уверен, что планы которые я симулировал руками истинные) ибо когда я сам смотрю все выполняется нормально... когда через джоб, то раз через раз зависание