SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


exception handling: how would you do it?


exception handling: how would you do it?

Author
Message
rightontarget
rightontarget
SSChasing Mays
SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)

Group: General Forum Members
Points: 645 Visits: 475
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,
pietlinden
pietlinden
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14165 Visits: 14135
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.
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4082 Visits: 3436
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
rightontarget
rightontarget
SSChasing Mays
SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)

Group: General Forum Members
Points: 645 Visits: 475
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;


AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4532 Visits: 3427
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
rightontarget
rightontarget
SSChasing Mays
SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)

Group: General Forum Members
Points: 645 Visits: 475
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;


mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10669 Visits: 7891
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
  • 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

  • dwain.c
    dwain.c
    SSCoach
    SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

    Group: General Forum Members
    Points: 17945 Visits: 6431
    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!
    My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
    Sean Pearce
    Sean Pearce
    SSCarpal Tunnel
    SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

    Group: General Forum Members
    Points: 4082 Visits: 3436
    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
    Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

    Group: General Forum Members
    Points: 216418 Visits: 41986
    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.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search