September 1, 2016 at 11:32 am
I've come across some T-SQL where a cursor is declared within a TRY block. Naturally its opened there and then iterated through doing what's was written to do. OK, I'm coming at this from a C# developer's point of view. I would think that the cursor's scope is restricted to the TRY scope. That it isn't even visible from within the CATCH block. But perhaps I'm wrong.
So, in T-SQL is the declaration of a cursor within a TRY block restricted to the TRY block?
FWIW, from within the CATCH block all that's there is a
SELECT 0
Kindest Regards, Rod Connect with me on LinkedIn.
September 1, 2016 at 12:02 pm
First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀
T-SQL is not C#. It's certainly easy enough to test.
DECLARE @i INT = 0
BEGIN TRY
DECLARE cur CURSOR FORWARD_ONLY
FOR
SELECT TOP 10 ROW_NUMBER() OVER( ORDER BY DB_NAME() ) AS rn
FROM INFORMATION_SCHEMA.TABLES
OPEN cur
FETCH NEXT FROM cur
SELECT 1/0
END TRY
BEGIN CATCH
FETCH NEXT FROM cur
END CATCH
FETCH NEXT FROM cur
CLOSE cur
DEALLOCATE cur
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 1, 2016 at 12:31 pm
drew.allen (9/1/2016)
First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀Drew
Second, cursors can be local or global and the default is defined by the database properties.
Third, Why would someone put a SELECT 0 in a CATCH block? It's like wanting to play Battletoads in Hard Mode.
September 1, 2016 at 1:15 pm
Thank you, Drew. To be honest with you I was afraid of modifying his code and potentially messing something up. Your example is an excellent one which "does no harm" and illustrates the point that the cursor is defined outside of the TRY block.
Kindest Regards, Rod Connect with me on LinkedIn.
September 1, 2016 at 1:19 pm
drew.allen (9/1/2016)
First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀T-SQL is not C#. It's certainly easy enough to test.
DECLARE @i INT = 0
BEGIN TRY
DECLARE cur CURSOR FORWARD_ONLY
FOR
SELECT TOP 10 ROW_NUMBER() OVER( ORDER BY DB_NAME() ) AS rn
FROM INFORMATION_SCHEMA.TABLES
OPEN cur
FETCH NEXT FROM cur
SELECT 1/0
END TRY
BEGIN CATCH
FETCH NEXT FROM cur
END CATCH
FETCH NEXT FROM cur
CLOSE cur
DEALLOCATE cur
Drew
And I will throw in, "but that depends." Yes cursors are evil, but sometimes they are the correct tool for a job.
September 1, 2016 at 1:44 pm
Luis Cazares (9/1/2016)
drew.allen (9/1/2016)
First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀Drew
Second, cursors can be local or global and the default is defined by the database properties.
Third, Why would someone put a SELECT 0 in a CATCH block? It's like wanting to play Battletoads in Hard Mode.
As a dummy statement, since T-SQL doesn't allow an empty block. They obviously just wanted to "cancel" any catchable error. Yeah, terrible idea, but it seems clear that's what they were doing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 1, 2016 at 1:46 pm
I don't know why he put a SELECT 0 in a CATCH block. FWIW, he also put a SELECT 1 at the bottom of the TRY block. Again, I'm thinking more like a C# developer than a SQL developer, because I'm thinking that the stored procedure will just exit at that point and nothing else will happen. I could be wrong about that. I suppose in a way that it doesn't matter because he doesn't close nor deallocate the cursor. He does has some interesting code at the very beginning of the SP:
-- Clean up any prior cursors
IF CURSOR_STATUS('global','Cur')>=-1
BEGIN
DEALLOCATE Cur
END
Well um, interesting.
Having said that I know that in the past I wrote T-SQL code using cursors and I know I sometimes forgot to close and deallocate the cursor. I intend to fix that omission.
Other than that I hate to re-write his code, because I feel like I'd be walking over his ideas. I'll just make sure it closes the cursor. And I'll also comment out that stuff at the very beginning in which he deallocates some cursor.
Kindest Regards, Rod Connect with me on LinkedIn.
September 1, 2016 at 1:47 pm
ScottPletcher (9/1/2016)
Luis Cazares (9/1/2016)
drew.allen (9/1/2016)
First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀Drew
Second, cursors can be local or global and the default is defined by the database properties.
Third, Why would someone put a SELECT 0 in a CATCH block? It's like wanting to play Battletoads in Hard Mode.
As a dummy statement, since T-SQL doesn't allow an empty block. They obviously just wanted to "cancel" any catchable error. Yeah, terrible idea, but it seems clear that's what they were doing.
Actually, our developers have used this:
begin try
do some work
end try
begin catch
end catch
Usually when inserting new values in to lookup tables.
September 1, 2016 at 1:57 pm
Lynn Pettis (9/1/2016)
ScottPletcher (9/1/2016)
Luis Cazares (9/1/2016)
drew.allen (9/1/2016)
First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀Drew
Second, cursors can be local or global and the default is defined by the database properties.
Third, Why would someone put a SELECT 0 in a CATCH block? It's like wanting to play Battletoads in Hard Mode.
As a dummy statement, since T-SQL doesn't allow an empty block. They obviously just wanted to "cancel" any catchable error. Yeah, terrible idea, but it seems clear that's what they were doing.
Actually, our developers have used this:
begin try
do some work
end try
begin catch
end catch
Usually when inserting new values in to lookup tables.
I hope that some day, people will understand error handling and stop using it as error hiding.
September 1, 2016 at 2:03 pm
Lynn Pettis (9/1/2016)
ScottPletcher (9/1/2016)
Luis Cazares (9/1/2016)
drew.allen (9/1/2016)
First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀Drew
Second, cursors can be local or global and the default is defined by the database properties.
Third, Why would someone put a SELECT 0 in a CATCH block? It's like wanting to play Battletoads in Hard Mode.
As a dummy statement, since T-SQL doesn't allow an empty block. They obviously just wanted to "cancel" any catchable error. Yeah, terrible idea, but it seems clear that's what they were doing.
Actually, our developers have used this:
begin try
do some work
end try
begin catch
end catch
Usually when inserting new values in to lookup tables.
I do it all the time when working with temp tables. (Although I remove it from the final code.) That allows me to easily rerun the query without an error saying the temp table already exists, especially if the query that populates the temp table is very long.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply