Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Шаблон написания вложенных хранимых процедур  [new]
HOTDOC
Member

Откуда:
Сообщений: 25
Хай пипл

Сразу же прошу прощения если кто-то захочет отправить меня в поиск НО
Нужно раз и навсегда узнать как правильно писать вложенные SP (TRAN)

Вариант который вроде как правильный звучит как

BEGIN TRANSACTION


...
IF @@ERROR <> 0 GOTO err
...
IF @@ERROR <> 0 GOTO err
...
IF @@ERROR <> 0 GOTO err
...
IF @@ERROR <> 0 GOTO err
...
IF @@ERROR <> 0 GOTO err

COMMIT TRANSACTION

RETURN

err: ROLLBACK TRANSACTION


Вроде как все замечательно, но

В такой конструкции

BEGIN TRANSACTION


select @@TRANCOUNT

BEGIN TRANSACTION
select @@TRANCOUNT
ROLLBACK TRANSACTION

select @@TRANCOUNT
commit TRANSACTION


Результат выполенния
1
2
0
+ The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

Возникает вопрос почему после после ROLLBACK @@TRANCOUNT становиться равным 0

РТФМ товарисчи

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.

И приходим к выводу что err надо подредактировать

err: IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION


Следующий вопрос как отследить неправльность выполнения вложенной хранимой процедуры (потаму как ROLLBACK TRANSACTION внутри хранимой процедуры меняет @@ERROR на 0 и ошибка не ловиться в родительской процедуре)

Ответ
Изменить окончание всех SP на

err: 

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR('',16, 1)


Вроде бы все.
А теперь госпада скажите все правильно или есть какие-либо дополнения и замечания!?
27 ноя 03, 11:34    [435697]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон написания вложенных хранимых процедур  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А ведь можно подглядеть как процедуры пишут сами разработчики MSSQL...

Вообще то процедуру можно завершать через RETURN(n), где n - код возврата, через который и можно передать ошибку или её отсутствие (0).
27 ноя 03, 11:38    [435711]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон написания вложенных хранимых процедур  [new]
HOTDOC
Member

Откуда:
Сообщений: 25
tpg писал:

Вообще то процедуру можно завершать через RETURN(n), где n - код возврата, через который и можно передать ошибку или её отсутствие (0).


Вообщемто можно, но тогда синтаксиз проверки прямых запросов и выполнения хранимых процедур будет различный. Когда будешь писать нужно об этом помнить (еще и на это отвлекаться). А так заранее навставлят кучу одинаковых строк и сиди между ними вставляй запросы процедуры и т.д.
ИМХО удобнее.

Но вот какой калапс обнаружился

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. Generic Error

Побороть его еще несумел, есть идеи
27 ноя 03, 12:32    [435887]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон написания вложенных хранимых процедур  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
http://support.microsoft.com/default.aspx?scid=kb;en-us;158325&Product=sql
27 ноя 03, 12:41    [435927]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон написания вложенных хранимых процедур  [new]
HOTDOC
Member

Откуда:
Сообщений: 25
По сути я делаю тоже самое что и MS

Во вложенной SP генерю ошибку. что приводит к ROLLBACKу транзакции во вложенной процебуре (и в родительской по идеи тоже). ROLLBACK задает @@TRANCOUNT = 0.

НО

B в родительской происходит ошибка из за того что процебула (вложенная) вызывалась из Транзакции, а это значит что для места вызова @@TRANCOUNT = 1, а после EXEC процедуры @@TRANCOUNT стал 0.

Все действия выполненные перед вызовом (вложенной) sp_ откатываются и единственное что в принципе напрягает это эта ошибка.

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. Rollback transaction in nested stored procedure

P.S. каждая процедура написана по шаблону приведенном выше
27 ноя 03, 13:12    [436052]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон написания вложенных хранимых процедур  [new]
funikovyuri
Member

Откуда: Симферополь
Сообщений: 4045
все таки при отсутсвии exception'ов я бы рекомендовал в большинстве случаев использовать SET XACT_ABORT ON
27 ноя 03, 13:21    [436072]     Ответить | Цитировать Сообщить модератору
 Re: Шаблон написания вложенных хранимых процедур  [new]
--------------
Guest
CREATE	PROCEDURE		Имя

@Ключ INT OUT
, @Параметр2 DATETIME
, @Параметр3 VARCHAR(30)
AS
SET NOCOUNT ON

DECLARE @PROCNAME SYSNAME SET @PROCNAME = OBJECT_NAME(@@PROCID)
DECLARE @TRANCOUNT INT SET @TRANCOUNT = @@TRANCOUNT
IF @TRANCOUNT = 0 BEGIN TRAN ELSE SAVE TRAN @PROCNAME

-- полезный текст


IF @TRANCOUNT = 0 COMMIT TRAN
RETURN 0
Err:
RAISERROR('%s ERROR', 16, 1, @PROCNAME)
IF @TRANCOUNT = 0 ROLLBACK TRAN ELSE ROLLBACK TRAN @PROCNAME
RETURN 1
27 ноя 03, 13:25    [436089]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить