Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Try/catch help

  1. #1
    Join Date
    Feb 2012
    Posts
    130

    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?

  2. #2
    Join Date
    Feb 2012
    Posts
    130
    Let add one more thing....could it be that statement1 contains an invald field and a try catch block can't account for that?

  3. #3
    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

  4. #4
    Join Date
    Feb 2012
    Posts
    130
    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?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,513
    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.

  6. #6
    Join Date
    Feb 2012
    Posts
    130
    Is there any way to handle parse errors?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,513
    Knowing your tables?

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

  8. #8
    Join Date
    Feb 2012
    Posts
    130
    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.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,513
    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.

  10. #10
    Join Date
    Feb 2012
    Posts
    130
    Lovely, that is what I was afraid of. Thank you for the guidance and useful infornation.

  11. #11
    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

  12. #12
    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

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    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.

  14. #14
    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

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •