Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Global Temp Tables: Triggers Vs Stored Procedure Expand / Collapse
Author
Message
Posted Monday, July 6, 2009 11:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 5:55 AM
Points: 56, Visits: 287
Hi All,

I am trying to access global temp table
1) Between stored procedure in a nested format(Stored Procedure Proc1 calling another stored procedure Proc2) and
2) between stored procedure called from a trigger

My Code goes as below:

Scenario 1) Here Proc1 calls another procedure Proc2. A global temp table ##ErrLogging is declared in Proc1 and is populated with the error message(if any) in the catch block of Proc2. The error message is visible even if the TRAN rollbacks in the catch block of Proc1.

Since the global temp table is intact even after the rollback statement; i would like to use this to insert into physical log table in my database.

CREATE PROCEDURE [dbo].[Proc1]
AS
BEGIN TRY
BEGIN TRAN

IF object_id('tempdb..##ErrLogging') is not null
DROP TABLE ##ErrLogging

CREATE TABLE ##ErrLogging(errMesg NVARCHAR(200))

EXEC Proc2
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT * FROM ##ErrLogging
END CATCH

GO


CREATE PROCEDURE [dbo].[Proc2]
AS
BEGIN TRY
RAISERROR('My Error',16,1)
END TRY
BEGIN CATCH
INSERT INTO ##ErrLogging VALUES('My Message');
END CATCH


Scenario: Trigger declare the global temp table and calls Proc2 as follows

CREATE TRIGGER [tr_OnInsertMy_Test] ON [dbo].[My_Test]
AFTER INSERT
AS
BEGIN TRY

BEGIN TRAN


IF object_id('tempdb..##ErrLogging') is not null
DROP TABLE ##ErrLogging

CREATE TABLE ##ErrLogging (errMesg NVARCHAR(200))

EXEC Proc2
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT * FROM ##ErrLogging


END CATCH

Proc2 remain same as in scenario one

But in this scenario; i faced with an error at the insert statement in the Proc2 mentioning "Invalid Object Name ##ErrLogging".

Is this the expectable result?Is there something am doing wrong here?
I need to access the global temp table in the catch block of Trigger in order to log error details into the physical table

Regards,
Post #747908
Posted Monday, July 6, 2009 12:05 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 3,844, Visits: 3,841
Is this a single user application? If not, how do you plan to keep the global temp table name unique for the thread that is creating it?

Using global temp tables for error handling is a bad idea. Why do you think you need to use a temp table to pass error information back to the calling SP?

BOL has a bunch of detailed examples of using TRY/CATCH blocks to process errors. I suggest you read them. You're going to get yourself into a world of hurt trying to use global temp tables to do this.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #747929
Posted Monday, July 6, 2009 12:51 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I'll second the warning on using a global temp for this. The moment two processes or users call the proc at the same time, you've got a problem.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #747975
Posted Monday, July 6, 2009 10:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 11,194, Visits: 11,135
I only looked quickly, I admit, but if you want to preserve log information over a transaction boundary, why don't you use a table variable instead?
Table variables aren't affected by transactions.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #748304
Posted Tuesday, July 7, 2009 2:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 5:55 AM
Points: 56, Visits: 287
Hi,

Can anyone help me out with writing the code(scenario 2) using temp variables for logging errors outside the transaction scope.

Thanks for your help!!!


Post #748367
Posted Tuesday, July 7, 2009 2:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 11,194, Visits: 11,135
If by 'helping you out' you mean 'write it for you' then...no - not me
(I already have a job!)

The best way to learn stuff is to take an idea and experiment with it. Create a table variable, start a transaction, insert some data into it, and then roll back your transaction. Finally, select everything from the table variable.

That should give you an idea how to proceed. If you get stuck, post back with your best attempt so far, and why you are stuck.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #748375
Posted Tuesday, July 7, 2009 6:52 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
naveenreddy.84 (7/7/2009)
Hi,

Can anyone help me out with writing the code(scenario 2) using temp variables for logging errors outside the transaction scope.

Thanks for your help!!!


Actually, for what you're doing, you don't need either. You're creating a single-row, nvarchar(200) value. Why not just assign it to an output parameter in the second proc? Then you can have the first proc get the value from that, and you're good to go.

Are you familiar with output parameters?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #748491
Posted Tuesday, July 7, 2009 11:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 3,844, Visits: 3,841
Or, create a standard error trapping method so you can always capture an error created in a nested TRY/CATCH block.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #748742
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse