exception handling: how would you do it?

  • Hi all,

    I have a 3-part UNION ALL query. Sometimes, one of the tables are not available, meaning I can't connect to it.

    Currently, if one part of the query fails, the whole thing fails. How do I trap all possible exceptions, so even if any 1 or 2 parts fail, the rest will return a result.

    Here is the test data:

    create table A (col_1 varchar(1), col_2 int);

    insert into A values('a',100);

    create table B (col_1 varchar(1), col_2 int);

    insert into B values('b',200);

    create table C (col_1 varchar(1), col_2 int);

    insert into C values('c',300);

    SELECT

    a.col_1AS td,

    a.col_2 AS

    FROM

    (

    select col_1, col_2 from A

    union all

    select col_1, col_2 from B

    union all

    select col_1, col_2 from C

    ) a

    FOR XML RAW('tr'), elements

    How would you do it?

    Thanks,

  • only thing i can think of is to do the iffy one inside a TRY/CATCH block and trap for the table not being available (if you can). Then use an IF statement to execute the proper SQL statement.

  • create table A (col_1 varchar(1), col_2 int);

    insert into A values('a',100);

    create table B (col_1 varchar(1), col_2 int);

    insert into B values('b',200);

    create table C (col_1 varchar(1), col_2 int);

    insert into C values('c',300);

    -- Use a temp table to store results

    CREATE TABLE #Results (col_1 varchar(1), col_2 int);

    -- Use try catch blocks for each source table and dynamic sql

    BEGIN TRY

    INSERT INTO #Results

    EXEC sp_executesql N'SELECT col_1, col_2 FROM A;';

    END TRY

    BEGIN CATCH

    PRINT 'Table A Does Not Exist';

    END CATCH;

    BEGIN TRY

    INSERT INTO #Results

    EXEC sp_executesql N'SELECT col_1, col_2 FROM B;';

    END TRY

    BEGIN CATCH

    PRINT 'Table B Does Not Exist';

    END CATCH;

    BEGIN TRY

    INSERT INTO #Results

    EXEC sp_executesql N'SELECT col_1, col_2 FROM C;';

    END TRY

    BEGIN CATCH

    PRINT 'Table C Does Not Exist';

    END CATCH;

    -- Return results

    SELECT

    col_1 AS td,

    col_2 AS

    FROM

    #Results

    FOR XML RAW('tr'), ELEMENTS;

    DROP TABLE #Results;

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Thank you, that looks like something I can try doing.

    One question: why do I have to use dynamic sql? Why can't I just:

    -- Use a temp table to store results

    CREATE TABLE #Results (col_1 varchar(1), col_2 int);

    -- Use try catch blocks for each source table and dynamic sql

    BEGIN TRY

    INSERT INTO #Results

    SELECT col_1, col_2 FROM A;

    END TRY

    BEGIN CATCH

    PRINT 'Table A Does Not Exist';

    END CATCH;

    BEGIN TRY

    INSERT INTO #Results

    SELECT col_1, col_2 FROM B;

    END TRY

    BEGIN CATCH

    PRINT 'Table B Does Not Exist';

    END CATCH;

    BEGIN TRY

    INSERT INTO #Results

    SELECT col_1, col_2 FROM C;

    END TRY

    BEGIN CATCH

    PRINT 'Table C Does Not Exist';

    END CATCH;

  • Sorry, but I do not see any example using Dynamic SQL. I see the examples using straight Transact SQL.

    I NEVER, EVER use Dynamic SQL. No matter what.

    Andrew SQLDBA

  • My bad, I was referring to

    INSERT INTO #Results

    EXEC sp_executesql N'SELECT col_1, col_2 FROM A;';

    can I use

    INSERT INTO #Results

    SELECT col_1, col_2 FROM A;

  • That is to prevent errors in compilation if a table does not exist.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (10/3/2013)


    That is to prevent errors in compilation if a table does not exist.

    If the code is in an SP, deferred resolution may save the day.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • AndrewSQLDBA (10/3/2013)


    Sorry, but I do not see any example using Dynamic SQL. I see the examples using straight Transact SQL.

    I NEVER, EVER use Dynamic SQL. No matter what.

    Andrew SQLDBA

    Dynamic SQL has it's place. In this example it's to prevent compilation errors. There are no avenues for injection in this code. I also use dynamic sql for maintenance operations.

    Just because a tool is widely misused doesn't mean it shouldn't be used at all.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Sean Pearce (10/4/2013)


    AndrewSQLDBA (10/3/2013)


    Sorry, but I do not see any example using Dynamic SQL. I see the examples using straight Transact SQL.

    I NEVER, EVER use Dynamic SQL. No matter what.

    Andrew SQLDBA

    Dynamic SQL has it's place. In this example it's to prevent compilation errors. There are no avenues for injection in this code. I also use dynamic sql for maintenance operations.

    Just because a tool is widely misused doesn't mean it shouldn't be used at all.

    +10

    --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)

  • Viewing 10 posts - 1 through 9 (of 9 total)

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