Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
grembos
Starting Member
7 Posts |
 Posted - 2011-12-13 : 22:51:44
|
| Does this pseudo example look correct. Suggestions or feedback welcome.... Thanks in advance------------------ CREATE PROCEDURE usp_hdr_detail_error_hdlr @id char(1) AS DECLARE @err int, BEGIN TRANSACTION-- calling another stored proc here; lets capture if its a Pass or a Fail EXEC @err = USP_some_other_sp @param1 SELECT @err = coalesce(nullif(@err, 0), @@error) IF @err <> 0 GOTO Fail INSERT hdr_tbl (...) SELECT @err = @@error IF @err <> 0 GOTO Fail UPDATE hdr_tbl SET status = 'HDR_OK' WHERE id = @id SELECT @err = @@error IF @err <> 0 GOTO Fail INSERT dtl_tbl (...) SELECT @err = @@error IF @err <> 0 GOTO Fail UPDATE dtlhdr_tbl SET status = 'DTL_OK' WHERE id = @id SELECT @err = @@error IF @err <> 0 GOTO Fail COMMIT TRANSACTION Fail: ROLLBACK TRANSACTION END RETURN @err |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-13 : 23:16:24
|
| And to add to that you've not declared @param1 anywhere in procedure------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
grembos
Starting Member
7 Posts |
Posted - 2011-12-13 : 23:35:10
|
Doesn't the FAIL: code block only get run when called with the GOTO Fail portion and skipped otherwise ?? .. thanks again****extracted from the original post.... UPDATE dtlhdr_tbl SET status = 'DTL_OK' WHERE id = @id SELECT @err = @@error IF @err <> 0 GOTO Fail COMMIT TRANSACTION Fail: ROLLBACK TRANSACTION END RETURN @errquote:
Originally posted by tkizer
You'll need to add a RETURN after the COMMIT, otherwise it'll do the COMMIT and then it'll error on the ROLLBACK.Is USP_some_other_sp properly coded to get you @err?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
grembos
Starting Member
7 Posts |
Posted - 2011-12-13 : 23:36:44
|
Just pseudo code for that part... thanks for you input though... appreciatedquote:
Originally posted by visakh16
And to add to that you've not declared @param1 anywhere in procedure------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-13 : 23:59:35
|
quote:
Originally posted by grembos
Doesn't the FAIL: code block only get run when called with the GOTO Fail portion and skipped otherwise ?? .. thanks again****extracted from the original post.... UPDATE dtlhdr_tbl SET status = 'DTL_OK' WHERE id = @id SELECT @err = @@error IF @err <> 0 GOTO Fail COMMIT TRANSACTION Fail: ROLLBACK TRANSACTION END RETURN @errquote:
Originally posted by tkizer
You'll need to add a RETURN after the COMMIT, otherwise it'll do the COMMIT and then it'll error on the ROLLBACK.Is USP_some_other_sp properly coded to get you @err?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Nope it will continue with fail block too without RETURN------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
?Err Handler (Transaction Commit \ Rollback)