Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

exception handling: how would you do it? Expand / Collapse
Author
Message
Posted Wednesday, October 2, 2013 3:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 104, Visits: 355
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_1 AS td,
a.col_2 AS [td align=RIGHT]
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,
Post #1500947
Posted Wednesday, October 2, 2013 11:59 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:28 PM
Points: 842, Visits: 5,433
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.
Post #1500994
Posted Thursday, October 3, 2013 2:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 943, Visits: 2,951
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 [td align=RIGHT]
FROM
#Results
FOR XML RAW('tr'), ELEMENTS;

DROP TABLE #Results;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1501054
Posted Thursday, October 3, 2013 10:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 104, Visits: 355
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;

Post #1501271
Posted Thursday, October 3, 2013 3:43 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:21 AM
Points: 977, Visits: 3,358
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
Post #1501366
Posted Thursday, October 3, 2013 4:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 104, Visits: 355
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;

Post #1501384
Posted Thursday, October 3, 2013 5:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 1,796, Visits: 5,801
That is to prevent errors in compilation if a table does not exist.

MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1501385
    Posted Thursday, October 3, 2013 6:43 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Tuesday, October 7, 2014 10:53 PM
    Points: 3,421, Visits: 5,359
    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!

    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?
    Since random numbers are too important to be left to chance, let's generate some!
    Learn to understand recursive CTEs by example.
    Splitting strings based on patterns can be fast!
    Post #1501395
    Posted Friday, October 4, 2013 3:06 AM


    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Yesterday @ 8:05 AM
    Points: 943, Visits: 2,951
    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

    @SeanPearceSQL

    About Me
    Post #1501473
    Posted Friday, October 4, 2013 2:10 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 9:25 PM
    Points: 35,372, Visits: 31,925
    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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1501768
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse