Try catch in a transaction block or transaction in try catch block???

Question

Try catch in a transaction block or transaction in try catch block???

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

SQL_By_Chance (10/13/2011)
CREATE PROCEDURE EXAMPLE (@parameter) ---- To illustrate how I intend to Use Execption Handling inside my Cursor AS BEGIN BEGIN TRY DECLARE @TABLENAME VARCHAR (10) DECLARE DROPTABLE CURSOR FOR SELECT NAME FROM INFORMATION_SCHEMA.TABLES -- OR SYSTABLES WHERE NAME = @parameter ---- Just an example OPEN DROP TABLE FETCH NEXT FROM DROPTABLE INTO @TABLENAME WHILE (@@FETCH_STATUS = 0) BEGIN BEGIN TRY DECLARE @sql VARCHAR (500) SET @sql = 'DROP TABLE ' + @TABLENAME DBNAME.SP_EXECUTESQL @sql END TRY BEGIN CATCH --- Capture error during execute @sql END CATCH FETCH NEXT FROM DROPTABLE INTO @TABLENAME END TRY BEGIN CATCH --- Capture error at proc level if any --- Capture error in case type mismatch or something else END CATCH END DEALLOCATE DROPTABLE CLOSE DROPTABLE
Just want basic template. Did I answer you correctly ?

Yowch! "Drop table" as an example? I truly hope than no one makes the mistake of running that code without actually looking at it to see what it does. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Jeff Moden (12/4/2011)
I truly hope than no one makes the mistake of running that code without actually looking at it to see what it does. 😉

Likewise, but not for the same reason.

Msg 102, Level 15, State 1, Procedure EXAMPLE, Line 1

Incorrect syntax near ')'.

Msg 156, Level 15, State 1, Line 9

Incorrect syntax near the keyword 'FOR'.

Msg 137, Level 15, State 2, Procedure EXAMPLE, Line 11

Must declare the scalar variable "@parameter".

Msg 156, Level 15, State 1, Line 12

Incorrect syntax near the keyword 'DROP'.

Msg 102, Level 15, State 1, Procedure EXAMPLE, Line 26

Incorrect syntax near 'DBNAME'.

Msg 102, Level 15, State 1, Procedure EXAMPLE, Line 39

Incorrect syntax near 'TRY'.

Msg 102, Level 15, State 1, Procedure EXAMPLE, Line 51

Incorrect syntax near 'DROPTABLE'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'NAME'.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 3

Hi Jeff/Gila Monster,

It was just an example 😛 to help someone who posted a query in some other thread for "How to delete tables within a DB following some pattern in naming convention"

I posted this here for GinaLuca to look and suggest me some template of using a Try..Catch within a proc to fetch error desc at Proc and Query level.

From next time onwards I would make sure I test the query before posting it(to avoid being screwed by 2 masters). 😀

Warm Regards,

Ankit

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”