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 02, 2013 3:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:51 PM
Points: 89, Visits: 300
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 02, 2013 11:59 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:20 AM
Points: 578, Visits: 3,755
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 03, 2013 2:37 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
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 03, 2013 10:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:51 PM
Points: 89, Visits: 300
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 03, 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: Yesterday @ 2:38 PM
Points: 958, Visits: 3,266
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 03, 2013 4:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:51 PM
Points: 89, Visits: 300
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 03, 2013 5:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,654, Visits: 5,208
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 03, 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: Today @ 2:24 AM
    Points: 3,591, Visits: 5,100
    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 04, 2013 3:06 AM


    Right there with Babe

    Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

    Group: General Forum Members
    Last Login: Monday, April 14, 2014 11:49 AM
    Points: 739, Visits: 2,470
    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 04, 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: Yesterday @ 4:00 PM
    Points: 35,967, Visits: 30,258
    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." -- 04 August 2013
    (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