Error checking while catching SCOPE_IDENTITY

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error checking while catching SCOPE_IDENTITY

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-09 : 05:43:49

In my stored procedure I am running lots of INSERT and UPDATE statements. I need to catch the SCOPE_IDENTITY value after the INSERTs to use elsewhere, and I also need to check for @@ERROR after ALL the statements, so that I can rollback the transaction if anything goes wrong.

Am I doing this correctly by checking for @@ERROR and then capturing SCOPE_IDENTITY, or does SCOPE_IDENTITY need capturing first?
I presume if you capture SCOPE_IDENTITY first, then the @@ERROR check is only looking for errors when capturing SCOPE_IDENTITY? Is that a correct assumption or is my entire method flawed anyway??!


CREATE PROC myProc
AS

  BEGIN TRAN

  DECLARE @lastID int
  DECLARE @errors int
  SET @errors = 0


  INSERT INTO ... (...) VALUES (...)
  IF @@ERROR <> 0 -- error check insert statement
     SET @errors = @errors + 1
  SET @lastID = SCOPE_IDENTITY() -- capture scope_identity


  UPDATE ... SET ... = ..., ....=....
  IF @@ERROR <> 0
    SET @errors = @errors + 1


  INSERT INTO ... (...) VALUES (...)
  IF @@ERROR <> 0
     SET @errors = @errors + 1
  SET @lastID = SCOPE_IDENTITY()

  ... rest of proc

  IF @errors <> 0
     BEGIN
        ROLLBACK TRAN
        RAISERROR('Something went really wrong', 10, 1)
        RETURN
     END

  COMMIT TRAN

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-09 : 06:48:29

As you are using SQL 2005, you can use OUTPUT instead if you like, that way you can capture every ID inserted instead of just the last one. You should also use TRY CATCH..


CREATE TABLE #TestRD (a int identity(1,1) not null, b varchar(100))
CREATE TABLE #Test (a int, b varchar(100))


BEGIN TRY
	INSERT INTO #TestRD
	OUTPUT
		  inserted.a
		, inserted.b
	INTO #Test
	SELECT 'v'
	UNION SELECT 'w'
	UNION SELECT 'x'
	UNION SELECT 'y'
	UNION SELECT 'z'


	SELECT * FROM #Test

	DROP TABLE #TestRD
	DROP TABLE #Test
END TRY

BEGIN CATCH
	PRINT 'Errored'
END CATCH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-09 : 07:03:24

You can perform multiple assignments to variables at the same time to make sure you get both. here is an example


DECLARE @error INT
DECLARE @rowNum INT

DECLARE @foo TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [val] CHAR(1)
	)

INSERT @foo ([val]) VALUES('a')

SELECT
	@error = @@ERROR
	, @rowNum = SCOPE_IDENTITY()

SELECT
	@error
	, @rowNum


INSERT @foo ([val]) VALUES('aa')

SELECT
	@error = @@ERROR
	, @rowNum = SCOPE_IDENTITY()

SELECT
	@error
	, @rowNum

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-09 : 07:22:28

Hi, and thanks very much for sharing your Yak knowledge. I was not aware that TRY CATCH had found its way into SQL - it certainly looks like the cleanest way to catch errors and I will begin using it immediately. Just two things though if I may...

1) Does my original approach fail to capture the error, or would it work?
2) Am I correct that TRY CATCH would be used in my original post as follows...?


CREATE PROC myProc
AS

BEGIN TRAN

	BEGIN TRY
  		DECLARE @lastID int

  		INSERT INTO ... (...) VALUES (...)
		SET @lastID = SCOPE_IDENTITY()

		UPDATE ... SET ... = ..., ....=....

		INSERT INTO ... (...) VALUES (...)
		SET @lastID = SCOPE_IDENTITY()

		... rest of proc
	END TRY

	BEGIN CATCH
		ROLLBACK TRAN
		RAISERROR('Something went really wrong', 10, 1)
		RETURN
	END CATCH

COMMIT TRAN

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-09 : 07:23:54

quote:
Originally posted by RickD

As you are using SQL 2005, you can use OUTPUT instead if you like, that way you can capture every ID inserted instead of just the last one.

If I'd have known about this a few weeks ago I would have spent so much less time sat at this desk and instead been out drinking beer...

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-09 : 09:03:54

1) Your original way should work, but its been a while since I used Scope_identity()..
2) Yes, that is the way you would use it, just be aware of the limitations with nesting (have a look in BOL for more).

3) Beer is always a better answer..

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-09 : 09:35:50

Hi RickD

I've taken your advice, and you are correct - beer is best.

Actually my example didn't quite work in practice. After reading some tutorials, it seems to need to have everything entirely located within the TRY or CATCH blocks, so it needs to be set out like this:


CREATE PROC myProc
AS

BEGIN TRY

	BEGIN TRAN

  		DECLARE @lastID int

  		INSERT INTO ... (...) VALUES (...)
		SET @lastID = SCOPE_IDENTITY()

		UPDATE ... SET ... = ..., ....=....

		INSERT INTO ... (...) VALUES (...)
		SET @lastID = SCOPE_IDENTITY()

		... rest of proc

	-- managed to get here without error, so commit
	COMMIT TRAN

END TRY

BEGIN CATCH
	-- quick escape...
	ROLLBACK TRAN
	RAISERROR('Something went really wrong', 10, 1)

	-- OR, get the error that was triggered by doing...
	ROLLBACK TRAN
	DECLARE @errMsg nvarchar(4000)
	DECLARE @errSvr int
	SET @errMsg = ERROR_MESSAGE()
	SET @errSvr = ERROR_SEVERITY()
	RAISERROR(@errMsg, @errSvr, 1)
END CATCH

Thanks all for your help in getting me to this stage!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-09 : 11:04:39

You are rolling back twice in your catch block. I doubt you actually want to do that. I imagine that you would receive the message in my signature.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-06-09 : 11:37:01

Hi

Yes and thank you - I appreciate your comment. I used the SQL comments to hopefully clarify that to anyone stumbling upon this via a search engine. So if you're reading this - use either one OR the other - NOT BOTH!

Kristen
Test

22859 Posts

Posted - 2010-06-09 : 12:25:55

We do


DECLARE ...
SELECT @intRetVal = 0    -- Assume no error

...

INSERT ...
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
IF @intErrNo <> 0 OR @intRowCount <> 1
BEGIN
    SELECT @intRetVal = -1,
           @strErrMsg = COALESCE(@strErrMsg + '.   ', '') 
               + 'SQL Error(-1) ... description of error ... . [' + CONVERT(varchar(20), @intErrNo) 
               + '/' + CONVERT(varchar(20), @intRowCount) + ']'
END
...
RETURN @intRetVal

where we have a unique value for each possible failpoint, returned as @intRetVal, so that we can diagnose precisely what broke / where, and we also have a "human readable" message (for application to display, or to be logged for later diagnosis)

Edit: Fixed Cut & Paste error

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-10 : 04:24:17

Kristen, never heard of sp_addmessage?

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 05:06:46

Yeah, but I definitely don't want the hassle of rolling out our custom messages to each site, nor polluting MASTER with our messages

We return our message, and intRetVal, as an OUTPUT parameter (as well as a RETURN value for @intRetVal) so that the caller (i.e. stack of calling SProcs, or [ultimately] application itself, can handle it as necessary)

No idea if that is best-method, but it seems to suit us

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources