If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Try/catch help

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
Try/catch help

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?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
Let add one more thing....could it be that statement1 contains an invald field and a try catch block can't account for that?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2001
Location: Chicago, Illinois, USA
Posts: 601
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?
__________________
Ken

Maverick Software Design

(847) 864-3600 x2
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
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?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,459
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
Is there any way to handle parse errors?
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,459
Knowing your tables?

How is a field name changing? Do you need to remove alter table rights from someone?
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,459
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
Lovely, that is what I was afraid of. Thank you for the guidance and useful infornation.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Sep 2001
Location: Chicago, Illinois, USA
Posts: 601
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.
__________________
Ken

Maverick Software Design

(847) 864-3600 x2
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Sep 2001
Location: Chicago, Illinois, USA
Posts: 601
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)
__________________
Ken

Maverick Software Design

(847) 864-3600 x2
Reply With Quote
  #13 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,789
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Sep 2001
Location: Chicago, Illinois, USA
Posts: 601
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?
__________________
Ken

Maverick Software Design

(847) 864-3600 x2
Reply With Quote
  #15 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,789
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On