Вот тебе пример моей ХП :

CREATE PROCEDURE dbo.PROCAddAuthUser @UserName varchar(25),
@UserPasswd varchar(25), @UserControl varchar(25),
@ERR int output AS

BEGIN tran AddAuthUser /*начало транзакции*/

/* затем какие-ио действия*/
Insert into AuthUser(UserName, UserPasswd, UserControl)
Values (@UserName, @UserPasswd, @UserControl)


if(@@error!=0) begin /*а вот здесь у меня проверка на ошибки. Это не обязательно!*/
rollback tran AddAuthUser
select @ERR=1
end
else begin
select @ERR=0
commit tran AddAuthUser
end
GO