June 25, 2015 at 3:46 pm
In the code below I have induced an error in my called sproc. I want the record version to not come back. I only want the error. The code that retrieves the output will only see the first output. Is there something I can change to make that happen?
IF OBJECT_ID ( N'CalledSproc', N'P' ) IS NOT NULL
DROP PROCEDURE CalledSproc;
GO
CREATE PROCEDURE CalledSproc (@Id varchar(10))
AS
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @TEMP as TABLE (Id int)
INSERT INTO @TEMP
VALUES (@Id)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
IF OBJECT_ID ( N'ExampleProc', N'P' ) IS NOT NULL
DROP PROCEDURE ExampleProc;
GO
CREATE PROCEDURE ExampleProc
AS
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @Id as Varchar(10) = 'Foo'
DECLARE @Temp Table (TableValue varchar(50), RecordVersion timestamp)
INSERT INTO @Temp (TableValue)
OUTPUT inserted.RecordVersion
Values(@Id)
EXECUTE CalledSproc @Id=@Id
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
EXECUTE ExampleProc
June 26, 2015 at 8:25 am
Not sure if this is going to produce the desired result, but looking at your code, you have an OUTPUT statement, and that isn't going to be part of a ROLLBACK. Produced rescordsets don't just disappear due to a ROLLBACK. See if this works:
IF OBJECT_ID ( N'CalledSproc', N'P' ) IS NOT NULL
DROP PROCEDURE CalledSproc;
GO
CREATE PROCEDURE CalledSproc (@Id varchar(10))
AS
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @TEMP as TABLE (Id int)
INSERT INTO @TEMP
VALUES (@Id)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
IF OBJECT_ID ( N'ExampleProc', N'P' ) IS NOT NULL
DROP PROCEDURE ExampleProc;
GO
CREATE PROCEDURE ExampleProc
AS
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @Id as Varchar(10) = 'Foo'
DECLARE @Temp Table (TableValue varchar(50), RecordVersion timestamp)
INSERT INTO @Temp (TableValue)
-- OUTPUT inserted.RecordVersion
Values(@Id)
EXECUTE CalledSproc @Id=@Id
SELECT * FROM @Temp
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
EXECUTE ExampleProc
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2015 at 8:44 am
I see where you are going with that. This was a simple example. We are inserting many rows and have an identity field. We use the output clause to capture the recordversion and the identity columns. I believe that is the optimized approach vs. doing a select after the insert. I really do not want to remove the output clause.
June 26, 2015 at 9:35 am
JKSQL (6/26/2015)
I see where you are going with that. This was a simple example. We are inserting many rows and have an identity field. We use the output clause to capture the recordversion and the identity columns. I believe that is the optimized approach vs. doing a select after the insert. I really do not want to remove the output clause.
Fortunately or unfortunately, the OUTPUT clause is the problem. It's not dependent on the presence or absence of an error. Until you can come up with a way to INSERT a record and simultaenously OUTPUT the value conditionally, you're code isn't going to do what you want. ROLLBACK isn't going to stop an OUTPUT clause, and there's no way to change that behavior that I can think of, short of causing an error on the INSERT. Once the INSERT succeeds, not even ROLLBACK can stop the OUTPUT clause.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2015 at 9:52 am
You could output into a table variable, and only select it if there's no error. You might want to test for any effects on performance, especially if the stored procedure runs frequently.
John
Edit - oops, that's already been suggested! (Although I'm struggling to see what the problem is with Steve's code.)
June 26, 2015 at 10:34 am
There is no problem with the code Steve presented. I just did not want the overhead of the select from a temp table. It seems like that will be the only way to make this work.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply