EXEC @err = AnyStoredProc @value
SET @save_error = @@error
-- NULLIF says that if @err is 0, this is the same as null
-- COALESCE returns the first non-null value in its arguments
SELECT @err = COALESCE( NULLIF(@err, 0), @save_error )
IF @err <> 0 BEGIN
-- Because stored proc may have started a tran it didn't commit
ROLLBACK TRANSACTION
RETURN @err
END
INSERT, DELETE, UPDATE
SELECT INTO
Invocation of stored procedures
invocation of dynamic SQL
COMMIT TRANSACTION
DECLARE and OPEN CURSOR
FETCH from cursor
WRITETEXT and UPDATETEXT
/*
Usage:
EXEC usp_ThisProc @Param1 = 1, @Param2 = 2
*/
SET NOCOUNT ON
BEGIN TRAN
INSERT...
UPDATE...
COMMIT
SET NOCOUNT ON
BEGIN TRAN
INSERT...
UPDATE...
COMMIT
SET NOCOUNT ON
BEGIN TRAN
INSERT...
IF @ErrorVar <> 0
BEGIN
RAISERROR(N'Message', 16, 1)
GOTO QuitWithRollback
END
UPDATE...
IF @ErrorVar <> 0
BEGIN
RAISERROR(N'Message', 16, 1)
GOTO QuitWithRollback
END
EXECUTE @ReturnCode = some_proc @some_param = 123
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
COMMIT
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
EndSave:
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
INSERT...
UPDATE...
COMMIT
END TRY
BEGIN CATCH
IF (XACT_STATE()) <> 0
ROLLBACK
END CATCH
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
INSERT...
UPDATE...
COMMIT