Try/catch help

jo15765 · #1 (**permalink**) 11-07-12, 16:05

I am running SQL SERVER 2005 --- I want to run a try catch statement to do this
BEGIN TRY
--If this statement does not execute succesfully I want to go to the catch
SQL STATEMENT 1
END TRY
BEGIN CATCH
SQL STATEMENT 2
END CATCH

Either statement 1 or statement 2 will be valid, but when I try that code, it only evaluates my Statement1 and then throws the INVALID COLUMN NAME message as opposed to jumping to the CATCH statement. Can someone explain why?

jo15765 · #2 (**permalink**) 11-07-12, 16:52

Let add one more thing....could it be that statement1 contains an invald field and a try catch block can't account for that?

PracticalProgram · #3 (**permalink**) 11-07-12, 20:44

I don't know the answer to your question, but it seems to me that if you think it is a possibility that SQL STATEMENT 1 might contain an invalid field name, why don't you just test that statement.

Each of the two SQL statements in your pseudo-code should be valid SQL statements, which should function independently of the TRY/CATCH blocks.

Is there something I am missing here?

jo15765 · #4 (**permalink**) 11-07-12, 21:19

Sometimes a field may abe firstName as opposed to fName so I was thinking if I used a try/catch block if it wasn't firstName then it would test the 2nd statement containing firstName. Am I going about it the wrong way?

MCrowley · #5 (**permalink**) 11-08-12, 09:19

One thing you have to remember, there are two phases where errors can occur. There is the parsing phase, where invalid columns are caught. Parsing errors are not handled by a try-catch block. Then there are execution errors (duplicate key, invalid key, check constraint violation, etc.) that are handled by the try-catch block.

jo15765 · #6 (**permalink**) 11-08-12, 09:29

Is there any way to handle parse errors?

MCrowley · #7 (**permalink**) 11-08-12, 10:04

Knowing your tables?

How is a field name changing? Do you need to remove alter table rights from someone?

jo15765 · #8 (**permalink**) 11-08-12, 11:25

It's a stored procedure that runs a select statement on multiple databases. So db1 could be fname while db2 is firstName. However the databases I need to query change frequently, so I was trying to think of a way to have the code check both fname and of that's incorrect check firstName.

MCrowley · #9 (**permalink**) 11-08-12, 13:09

Well, you have learned the first rule of automation, anyway: Consistency is critical.

I doubt the problem is just going to be on one column, or limited to the two variations, so you will need some way of mapping column names to their meaning. Probably a table with columns detailing the database/column/meaning combinations. Using that means you would be building up the query to be run with various string concatenation statements, and running what is normally referred to as dynamic SQL. Very painful to troubleshoot. In fact, I would highly encourage you to move the developers responsible for the variations to get into compliance. It may end up easier.

jo15765 · #10 (**permalink**) 11-08-12, 13:59

Lovely, that is what I was afraid of. Thank you for the guidance and useful infornation.

PracticalProgram · #11 (**permalink**) 11-09-12, 06:56

You could query sysobjects and syscolumns for the existence of the named columns, which would accomplish the same thing as your try/catch. Depending upon the results of that existence test, you would run either the fname query or the firstname query. Obviously, cleaning-up the databases would be the ideal solution, but sometimes you have to do what you have to do.

PracticalProgram · #12 (**permalink**) 11-12-12, 11:00

Just to expand on my previous posting on this subject, here is the kind of code I would use to get past your problem. The ideal solution, and the solution you probably should take, is to correct the original, "architectural" problem. But if you can't, you can use this methodology to get-around your problem. I use dynamic SQL to get-around and parsing issues.

Code:

declare @SQL varchar(1000)
declare @IsFname bit

select	@IsFname=COUNT(*)
from	sysobjects SO
inner
join	syscolumns sC on
			sC.id=sO.id
where	sO.name='PREmployees'
		and sC.name='fname'

if (@IsFname<>0)
	set @SQL=
		'
		select	fname
		from	dbo.PREmployees
		'
else
	set @SQL=
		'
		select	firstname
		from	dbo.PREmployees
		'

exec(@SQL)

Pat Phelan · #13 (**permalink**) 11-12-12, 12:19

To answer your original question first:

Code:

/*------------------------
IF Object_Id('LowerLevel', 'P') IS NOT NULL
   DROP PROCEDURE dbo.LowerLevel
GO
--  ptp  20121112  Create procdure to contain error at a lower level in the call stack

CREATE PROCEDURE dbo.LowerLevel
AS
BEGIN
   SELECT fName FROM PREmployees
END
GO
--  ptp  20121112  Show how to trap the error using a LowerLevel procedure

BEGIN TRY
   EXECUTE dbo.LowerLevel
   PRINT 'Succeeded????'
END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE()
,     ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE()
END CATCH
GO
--  ptp  20121112  Show how to fail to catch error at the current call stack level

BEGIN TRY
   SELECT FirstName FROM PREmployees
   PRINT 'Succeeded????'
END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE()
,     ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE()
END CATCH
GO
DROP PROCEDURE dbo.LowerLevel
------------------------*/
                                                                                                                                                                                 
----------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
208         16          1           LowerLevel                                                                                                                       6           Invalid object name 'non_existant_table'.

(1 row(s) affected)

Msg 208, Level 16, State 1, Line 4
Invalid object name 'non_existant_table'.

Moving on to your second question, in SQL 2012 Microsoft introduced TRY_PARSE (Transact-SQL) which directly addresses that issue.

If I were coding this particular problem in SQL 2005, I would simply query INFORMATION_SCHEMA.ROUTINES and proceed accordingly. Something like:

Code:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   WHERE  'fName' = COLUMN_NAME
      AND 'PREmployees' = TABLE_NAME)
   SELECT fName FROM PREmployees
ELSE
   SELECT FirstName FROM PREmployees

-PatP

PracticalProgram · #14 (**permalink**) 11-12-12, 17:01

I like that you have simplified this with the use of the INFORMATION_SCHEMA.COLUMNS view. I've never used the INFORMATION_SCHEMAs, but I'm going to start incorporating themn now.

But I do see a problem with a solution that does not use dynamic SQL. Without dynamic SQL, doesn't the entire SQL need to be parsable? With only fName or FirstName existing, but not the other, won't this SQL fail?

It fails when I test it.

Because my PREmployees table has fName, but not FirstName, when you run this SQL the entire batch fails because FirstName is not a valid field. Conversely, if PREmployees has a FirstName field, the entire batch would fail because fName is not a valid field.

Am I looking at this incorrectly?

Pat Phelan · #15 (**permalink**) 11-12-12, 17:22

I usually build stored procedures, which will compile with a warning message either way, but won't fail as long as you don't execute the code that would have failed. That is how I built the LowerLevel procedure, it didn't care that the table and column didn't exist at compile time.

I don't have a copy of SQL 2005 handy to test with, but the code works as published on my laptop running SQL 2012. I would expect it to run "as is" on SQL 2005 as well.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech